/* @(#)GSOrderDAO.java
 *
 * Project: BUS
 *
 * Modify Information:
 * =============================================================================
 *   Author       Date       Description
 *   ------------ ---------- ---------------------------------------------------
 *   Simonyi Peng 2006-2-7    建立
 *   Jenny   Li   2006-2-16  添加方法queryUncommitOrderAndDetail，queryOrder，updateOrderStatus，queryOrderByOpt
 * 	simonyi peng 2006-2-16  添加方法selectOrderByCode
 *   Road Zheng   2006-2-16  添加方法getOrderByTempID
 *   Simonyi Peng 2006-3-3   添加方法queryOrderAndDetail，queryOrderAndDetailForAuthorize，修改方法insert，
 *   Jenny   Li   2006-3-14  添加方法queryUncommitOrderAndDetail(Connection,GSOrderSubmitVO)
 *   Jenny   Li   2006-3-16  修改方法queryUncommitOrderAndDetail查询订单明细状态为复审通过
 *   owen   xia   2006-3-16  添加方法queryCancelOrder，modifyCancelOrder
 *   Jenny   Li   2006-3-29  修改方法getOrderDeliverStatusList,getOrderReceiveStatusList,getOrderDetailList
 *                           添加方法getOrderDeliverStatuslstCount,getOrderDeliverStatusSql,
 *                           getOrderRecStatuslstCount,getOrderReceiveStatusLstSql
 *                           getOrderDetailListCount,getOrderDetailListSql
 *   Jenny   Li   2006-4-17  添加方法insertOrderFromTemp
 *   Simonyi Peng 2006-10-13 添加方法getOrderDetailListSqlOfBusQryForBalanceAudit,getOrderDetailListForBalanceAudit支持结算审核
 *   Simonyi Peng 2006-10-23 添加方法getOrderDetailListCountForBalanceAudit支持结算审核查询
 *   Tuthmos      2008-06-18 更改查询日期范围
 * Copyright Notice:
 * =============================================================================
 *    Copyright (c) 2001-2006 Beijing HiTRUST Technology Co., Ltd. 1808 Room,
 *        Science & Technology Building, No. 9 South Street, Zhong Guan Cun,
 *        Haidian District, Beijing ,100081, China All rights reserved.
 *
 *    This software is the confidential and proprietary information of
 *        Beijing HiTRUST Technology Co., Ltd. ("Confidential Information").
 *        You shall not disclose such Confidential Information and shall use it
 *        only in accordance with the terms of the license agreement you entered
 *        into with HiTRUST.
 *
 * Warning:
 * =============================================================================
 * 
 */
package com.hitrust.gs.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import system.LoginContext;
import system.base.Warehouse;

import com.hitrust.gs.bean.GSBalanceParamBean;
import com.hitrust.gs.bean.GSManufactureBean;
import com.hitrust.gs.bean.GSOperatorBean;
import com.hitrust.gs.bean.GSOrderAnaDetailAndCategoryBean;
import com.hitrust.gs.bean.GSOrderAnaDetailAndCategoryNewBean;
import com.hitrust.gs.bean.GSOrderAnaDetailBean;
import com.hitrust.gs.bean.GSOrderApplyBean;
import com.hitrust.gs.bean.GSOrderBean;
import com.hitrust.gs.bean.GSOrderDetailBean;
import com.hitrust.gs.bean.GSProviderBean;
import com.hitrust.gs.bean.GSTempOrderBean;
import com.hitrust.gs.business.GSManufactureProcessor;
import com.hitrust.gs.business.GSOrderProcesser;
import com.hitrust.gs.form.GSOrderDetailQueryForm;
import com.hitrust.gs.form.GSOrderStatusTraceQueryForm;
import com.hitrust.gs.lib.GSBaseConstants;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.GSSystemConfigurator;
import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.util.date.DateUtil;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.util.lang.IDGenerator;
import com.hitrust.gs.util.lang.StringUtils;
import com.hitrust.gs.vo.GSBusOrderDetailDeliverVO;
import com.hitrust.gs.vo.GSDeliverOrderSaveVO;
import com.hitrust.gs.vo.GSOrderAuditVO;
import com.hitrust.gs.vo.GSOrderAuthorizeVO;
import com.hitrust.gs.vo.GSOrderCancelVO;
import com.hitrust.gs.vo.GSOrderDeliverStatusVO;
import com.hitrust.gs.vo.GSOrderDetailQueryForSWVO;
import com.hitrust.gs.vo.GSOrderDetailQueryVO;
import com.hitrust.gs.vo.GSOrderEmergencyOfWorkingVO;
import com.hitrust.gs.vo.GSOrderEmergencyVO;
import com.hitrust.gs.vo.GSOrderManufactureVO;
import com.hitrust.gs.vo.GSOrderStatusTraceQryVO;
import com.hitrust.gs.vo.GSOrderSubmitOfWorkingVO;
import com.hitrust.gs.vo.GSOrderSubmitVO;
import com.hitrust.gs.vo.GSOrderVO;

import framework.core.DBConnection;

/**
 * @author simonyi
 * 
 * TODO To change the template for this generated type comment go to Window -
 * Preferences - Java - Code Style - Code Templates
 */
public class GSOrderDAO extends GSBaseDAO {
	/**
	 * 根据订单编号查询订单bean
	 * 
	 * @param orderCode
	 * @return
	 * @throws GSException
	 */
	public GSOrderBean selectOrderByCode(String orderCode) throws GSException {
		GSOrderBean tGSOrderBean = new GSOrderBean();
		String sql = "select * from gs_order where gs_order_tempid = ?";

		ArrayList tList = null;

		Object[] tObj = { orderCode };
		try {
			tList = this.executeQuery(new GSOrderBean(), sql, tObj);
			if (tList.size() > 0) {
				tGSOrderBean = (GSOrderBean) tList.get(0);
			} else {
				// throw new
				// GSException(GSException.BME20041,GSException.BME20041_MSG);
			}
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E10040, GSException.E10040_MSG);
		}

		return tGSOrderBean;

	}

	/**
	 * 建立一个新的订单
	 * @param vo 订单数据值对象
	 * @param ary 订单详细数据对象矢量
	 * @throws GSException
	 */
	public String insert(LoginContext context,GSOrderVO vo,ArrayList ary,String sign) throws GSException {

		Connection conn = getConnetion();

		IDGenerator idg = new IDGenerator();

		String returnValue = "";
		try{
			conn.setAutoCommit(false);

			//构造订单号
			long id = idg.generatorID(vo.orderDeptId,"01");
			String orderId =sign+vo.orderDeptId + "_" + idg.formatID(id,5);//sign标识区分要货申请、补货申请、正常采购订单类型
			returnValue = orderId;

			//0 如果选择了直接审批通过，将订单(明细)状态修正为'3'复审通过，否则'1'需要审批
			String isReviewNeeded = "on".equals(vo.isReviewNeeded)?"3":"1";

			//1 构造生成时间
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String createDate = sdf.format(new Date(System.currentTimeMillis()));

			//记录系统实际时间
			String systemCreateDate = createDate;

			//表示需要更改订单创建时间
			if (vo.orderCreateDate.equals("true"))
			{
				GSBalanceParamDAO dao = new GSBalanceParamDAO();
				GSBalanceParamBean tBean = new GSBalanceParamBean();
				tBean = dao.queryBalanceDate();
				String strEnd = tBean.getGs_balance_end_date();
				if (strEnd.length() ==1) {
					strEnd += "0";
				}
				createDate = createDate.substring(0,8) + strEnd + createDate.substring(10);
			}

			//2 构造订单明细sql语句
			PreparedStatement pstmt = null;
			String sqlm = "";

			sqlm = "insert into GS_ORDER_DETAIL (GS_ORDER_TEMPID,GS_ORDER_DETAIL_ID," +
			"GS_MANUFACTURE_ID,GS_CONCENTRICITY_ID,GS_ORDER_COUNT," +
			"GS_DELIVER_GOODS_DATE,GS_DELIVER_GOODS_TIME,GS_ORDER_DETAIL_STATUS," +
			"GS_PROVIDER_ID,GS_LASTMODIFER_ID,GS_LASTMODIFY_CONTENTS,GS_LASTMODIFY_DATE," +
			"GS_GOODS_UNITPRICE,GS_IS_BALANCEED,GS_IS_PROV_BALANCEED,GS_BALANCE_CATEGORY_NO,gs_balance_mode) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sqlm);

			//3 计算订单总价
			double totalPrice = 0;
			BigDecimal buf = new BigDecimal("0");

			GSManufactureProcessor mp = new GSManufactureProcessor();
			//4 对每一个产品
			for(int i=0;i<ary.size();i++){
				GSOrderManufactureVO dvo = (GSOrderManufactureVO)ary.get(i);
				/**
				 * 处理申沃订单,如果是直接提交则状态改成初审通过
				 */
				if(dvo.providerId.equals("90001")&&isReviewNeeded.equals("3")){
					isReviewNeeded="2";
					break;
				}
			}
			//4 对每一个产品
			for(int i=0;i<ary.size();i++){
				GSOrderManufactureVO dvo = (GSOrderManufactureVO)ary.get(i);
				String manufactureId = mp.getManufactureIdByConcentricityIdAndProviderId(dvo.concentricityId,dvo.providerId);
				GSManufactureBean tBean = mp.getManufactureDetailByConcentricityCode(dvo.concentricityId);
		
				//计算产品的总价
				BigDecimal bd = new BigDecimal(new Double(tBean.getGs_manufacture_unitprice()).toString());
				//订单总价=产品单价*产品数量+初始订单总价
				buf = bd.multiply(new BigDecimal(""+dvo.ordercount)).add(buf);

				//构造订单明细信息号
				String orderDetailId = orderId + idg.formatID(i,2);

				pstmt.setString(1,orderId);
				pstmt.setString(2,orderDetailId);
				pstmt.setString(3,manufactureId);
				pstmt.setString(4,dvo.concentricityId);
				pstmt.setDouble(5,Double.parseDouble(dvo.ordercount));
				pstmt.setString(6,dvo.deliverdate.replace('/','-'));
				pstmt.setString(7,dvo.delivertime);
				pstmt.setString(8,isReviewNeeded);
				pstmt.setString(9,dvo.providerId);
				pstmt.setString(10,context.getLoginUserId());
				pstmt.setString(11,"申购单录入");
				pstmt.setString(12,createDate);
				pstmt.setDouble(13,tBean.getGs_manufacture_unitprice());
				pstmt.setString(14,"0");//表示尚未纳入结算数据
				pstmt.setString(15,"0");//表示尚未纳入供应商结算数据
				pstmt.setString(16,dvo.balanceCategory);//结算分类
				pstmt.setString(17, dvo.mode);//结算模式
				//存入数据库
				pstmt.addBatch();
			}


			//5 构造订单sql语句
			String sqlo = "";
			if(isReviewNeeded.equals("2")){//直接审批通过的处理(设置初审为直接审批通过人)
				sqlo = "insert into GS_ORDER (GS_ORDER_TEMPID,GS_WORKCORP_ID,"+
				"GS_ORDER_CATEGORY,GS_ORDER_STATUS,GS_NOTES,GS_RECORDER_ID,"+
				"GS_ORDER_CREATE_DATE,GS_ORDER_SUM,GS_DELIVER_GOODS_ADDR," +
				"GS_ORDER_CONTACT_NAME,GS_ORDER_CONTACT_PHONE," +
				"GS_DELIVER_MODE,GS_BALANCE_MODE,"+
				"GS_FIRSTAUDITOR_ID,GS_FIRSTAUDIT_DATE"+
				") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				
			}else if (isReviewNeeded.equals("3"))//直接审批通过的处理(设置初审和复审均为直接审批通过人)
			{
				sqlo = "insert into GS_ORDER (GS_ORDER_TEMPID,GS_WORKCORP_ID,"+
				"GS_ORDER_CATEGORY,GS_ORDER_STATUS,GS_NOTES,GS_RECORDER_ID,"+
				"GS_ORDER_CREATE_DATE,GS_ORDER_SUM,GS_DELIVER_GOODS_ADDR," +
				"GS_ORDER_CONTACT_NAME,GS_ORDER_CONTACT_PHONE," +
				"GS_DELIVER_MODE,GS_BALANCE_MODE,"+
				"GS_FIRSTAUDITOR_ID,GS_FIRSTAUDIT_DATE,"+
				"GS_REVIEWER_ID,GS_REVIEW_DATE) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			}
			else
			{
				//非直接审批通过的处理情况(也是原来的处理方法)
				sqlo = "insert into GS_ORDER (GS_ORDER_TEMPID,GS_WORKCORP_ID,"+
				"GS_ORDER_CATEGORY,GS_ORDER_STATUS,GS_NOTES,GS_RECORDER_ID,"+
				"GS_ORDER_CREATE_DATE,GS_ORDER_SUM,GS_DELIVER_GOODS_ADDR," +
				"GS_ORDER_CONTACT_NAME,GS_ORDER_CONTACT_PHONE," +
				"GS_DELIVER_MODE,GS_BALANCE_MODE) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
			}
			PreparedStatement pstmto = conn.prepareStatement(sqlo);
			pstmto.setString(1,orderId);
			pstmto.setString(2,vo.orderDeptId);
			pstmto.setString(3,"0");
			pstmto.setString(4,isReviewNeeded);
			pstmto.setString(5,vo.notes);
			pstmto.setString(6,vo.operid);
			pstmto.setString(7,createDate);
			pstmto.setDouble(8,buf.doubleValue());
			pstmto.setString(9,vo.deliverAddr);
			pstmto.setString(10,vo.linkMan);
			pstmto.setString(11,vo.linkTel);
			pstmto.setString(12,vo.deliverMode);
			pstmto.setString(13,vo.balanceMode);

			//直接审批通过的处理(设置初审和复审均为直接审批通过人)
			if (isReviewNeeded.equals("3"))
			{
				pstmto.setString(14,context.getLoginUserId());
				pstmto.setString(15,createDate);
				pstmto.setString(16,context.getLoginUserId());
				pstmto.setString(17,createDate);
			}else if (isReviewNeeded.equals("2"))
			{
				pstmto.setString(14,context.getLoginUserId());
				pstmto.setString(15,createDate);
			}
			pstmto.executeUpdate();

			//6 提交订单明细
			pstmt.executeBatch();
			conn.commit();
			if(pstmt!=null) {
				pstmt.close();
			}
			if(pstmto!=null) {
				pstmto.close();
			}

		}catch(Exception e){
			try{
				conn.rollback();
			}catch(SQLException sqle){}
			throw new GSException (GSException.E20051,GSException.E20051_MSG);
		}finally{
			try{
				conn.setAutoCommit(true);
			}catch(SQLException sqle){}
			close(conn);
		}

		return returnValue;

	}
	/**申请单转换为订单保存**/
	public String[] insertApply(LoginContext context,ArrayList tList) throws GSException {
		Connection conn = getConnetion();
		IDGenerator idg = new IDGenerator();
		DBConnection dbcon=null;
		PreparedStatement pstmto=null;
		PreparedStatement pstmt = null;
		PreparedStatement ps=null;
		String[] temp=new String[tList.size()];
		try{
			dbcon=DBConnection.open(true);
			conn.setAutoCommit(false);
			for (int i = 0; i < tList.size(); i++) {
				String returnValue = "";
				GSOrderApplyBean applyBean=(GSOrderApplyBean) tList.get(i);
				//根据仓库编号获取仓库信息
				Warehouse ware=Warehouse.fetchByCode(dbcon, applyBean.getWarehouse_code());
				//构造订单号
				long id = idg.generatorID(applyBean.getWarehouse_code(),"01");
				String orderId =GSBaseConstants.ORDER_GIVE+applyBean.getWarehouse_code() + idg.formatID(id,5);
				returnValue = orderId;

				String isReviewNeeded =GSBaseConstants.ORDER_SECONDAUDIT_PASS;

				//1 构造生成时间
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				String createDate = sdf.format(new Date(System.currentTimeMillis()));
				//2 构造订单明细sql语句

				String sqlm = "";

				sqlm = "insert into GS_ORDER_DETAIL (GS_ORDER_TEMPID,GS_ORDER_DETAIL_ID," +
				"GS_MANUFACTURE_ID,GS_CONCENTRICITY_ID,GS_ORDER_COUNT," +
				"GS_DELIVER_GOODS_DATE,GS_DELIVER_GOODS_TIME,GS_ORDER_DETAIL_STATUS," +
				"GS_PROVIDER_ID,GS_LASTMODIFER_ID,GS_LASTMODIFY_CONTENTS,GS_LASTMODIFY_DATE," +
				"GS_GOODS_UNITPRICE,GS_IS_BALANCEED,GS_IS_PROV_BALANCEED,GS_BALANCE_CATEGORY_NO,gs_balance_mode) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				pstmt = conn.prepareStatement(sqlm);
				//3 计算订单总价
				BigDecimal buf = new BigDecimal("0");
				//计算产品的总价
				BigDecimal bd = new BigDecimal(new Double(applyBean.getUnit_price()).toString());
				//订单总价=产品单价*产品数量+初始订单总价
				buf = bd.multiply(new BigDecimal(""+applyBean.getSend_count())).add(buf);
				//构造订单明细信息号
				String orderDetailId = orderId + idg.formatID(i,2);
				pstmt.setString(1,orderId);
				pstmt.setString(2,orderDetailId);
				pstmt.setString(3,applyBean.getApep_code());
				pstmt.setString(4,applyBean.getConcentricity_code());
				pstmt.setDouble(5,applyBean.getSend_count());
				pstmt.setString(6,applyBean.getSend_date().replace('/','-'));
				pstmt.setString(7,applyBean.getSend_time());
				pstmt.setString(8,isReviewNeeded);
				pstmt.setString(9,applyBean.getProvider_code());
				pstmt.setString(10,context.getLoginUserId());
				pstmt.setString(11,"补货申请生成订单");
				pstmt.setString(12,createDate);
				pstmt.setDouble(13,applyBean.getUnit_price());
				pstmt.setString(14,"0");//表示尚未纳入结算数据
				pstmt.setString(15,"0");//表示尚未纳入供应商结算数据
				pstmt.setString(16,"0");//结算分类
				pstmt.setString(17, GSBaseConstants.BALANCE_MODE1);//结算模式
				//5 构造订单sql语句
				String sqlo = "insert into GS_ORDER (GS_ORDER_TEMPID,GS_WORKCORP_ID,"+
				"GS_ORDER_CATEGORY,GS_ORDER_STATUS,GS_NOTES,GS_RECORDER_ID,"+
				"GS_ORDER_CREATE_DATE,GS_ORDER_SUM,GS_DELIVER_GOODS_ADDR," +
				"GS_ORDER_CONTACT_NAME,GS_ORDER_CONTACT_PHONE," +
				"GS_DELIVER_MODE,GS_BALANCE_MODE,"+
				"GS_FIRSTAUDITOR_ID,GS_FIRSTAUDIT_DATE,"+
				"GS_REVIEWER_ID,GS_REVIEW_DATE) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

				pstmto = conn.prepareStatement(sqlo);
				pstmto.setString(1,orderId);
				pstmto.setString(2,applyBean.getWarehouse_code());
				pstmto.setString(3,"0");
				pstmto.setString(4,isReviewNeeded);
				pstmto.setString(5,"补货申请同意配送");
				pstmto.setString(6,context.getLoginUserId());
				pstmto.setString(7,createDate);
				pstmto.setDouble(8,buf.doubleValue());
				pstmto.setString(9,ware.getAddress());
				pstmto.setString(10,ware.getContactPerson());
				pstmto.setString(11,ware.getContackTel());
				pstmto.setString(12,"100");
				pstmto.setString(13,"200");
				pstmto.setString(14,context.getLoginUserId());
				pstmto.setString(15,createDate);
				pstmto.setString(16,context.getLoginUserId());
				pstmto.setString(17,createDate);
				pstmto.addBatch();


				//更新申请单
				String sql="update t_order_apply set apply_stauts=?,APPLY_NOAGREE_REASON=?,LAST_UPDATE_USER_ID=?,LAST_UPDATE_USER=?,LAST_UPDATE_TIME=?,order_id=? where apply_code=?";
				ps=conn.prepareStatement(sql);
				ps.setString(1, GSBaseConstants.ORDER_SUPPLY_AGREE);
				ps.setString(2, "同意补货配送");
				ps.setString(3, context.getLoginUserId());
				ps.setString(4, context.getLoginUserName());
				ps.setString(5, createDate);
				ps.setString(6, orderId);
				ps.setString(7, applyBean.getApply_code());
				ps.addBatch();
				temp[i]=returnValue;
			}
			//6 写入
			pstmt.executeBatch();
			pstmto.executeBatch();
			ps.executeBatch();
			conn.commit();
		}catch(Exception e){
			try{
				conn.rollback();
			}catch(SQLException sqle){}
			throw new GSException (GSException.E20051,GSException.E20051_MSG);
		}finally{
			try{
				if(pstmt!=null) {
					pstmt.close();
				}
				if(pstmto!=null) {
					pstmto.close();
				}
				conn.setAutoCommit(true);
				dbcon.close();
			}catch(Exception sqle){}
			close(conn);

		}

		return temp;

	}
	/**
	 * 获得一个订单的所有订单详细数据对象矢量
	 * @param orderId 已知的订单号
	 * @return
	 * @throws GSException
	 */
	public ArrayList getManufactures(String orderId) throws GSException {
		ArrayList ary = null;
		try{
			String sql = "select * from gs_order_detail where gs_order_tempid = ?";
			GSOrderDetailBean detailBean = new GSOrderDetailBean();
			Object[] obj = {orderId};
			ary = this.executeQuery(detailBean,sql,obj);
		}catch(Exception sqle){
			throw new GSException (GSException.E20043,GSException.E20043_MSG);
		}

		return ary;
	}

	/**
	 * 查询订单及明细信息
	 * 
	 * @param aConn
	 *            DB连接
	 * @param aGSOrderVO
	 *            订单信息VO对象
	 * @return
	 * @throws GSException
	 */
	public ArrayList queryUncommitOrderAndDetail(String sql,GSOrderSubmitVO aGSOrderVO) throws GSException {
		StringBuffer tSql = new StringBuffer();
		ArrayList tList = null;
		tSql.append(" and det.GS_ORDER_DETAIL_STATUS='3'");
		if (aGSOrderVO.orderid != null && aGSOrderVO.orderid.trim().length() > 0) {
			tSql.append(" and ord.GS_ORDER_TEMPID like '%" + aGSOrderVO.orderid.trim() + "%'");
		}
		if (aGSOrderVO.concentricityId != null && aGSOrderVO.concentricityId.trim().length() > 0) {
			tSql.append(" and det.GS_CONCENTRICITY_ID like '%" + aGSOrderVO.concentricityId.trim() + "%'");
		}
		//图件号
		if(aGSOrderVO.drawingpiececode != null && !aGSOrderVO.drawingpiececode.equals("")){
			tSql.append(" and pro.DRAWING_PIECE_CODE like '%" + aGSOrderVO.drawingpiececode.trim() + "%'");
		}
		if (aGSOrderVO.category != null && aGSOrderVO.category.length()>0) {
			if (aGSOrderVO.category.equals("00")) {
				tSql.append(" and ord.GS_ORDER_CATEGORY like '%'");
			} else {
				tSql.append(" and ord.GS_ORDER_CATEGORY like '%" + aGSOrderVO.category + "%'");
			}
		}
		if (aGSOrderVO.orderDeptId != null && aGSOrderVO.orderDeptId.trim().length() > 0) {
			tSql.append(" and ord.GS_WORKCORP_ID like'" + aGSOrderVO.orderDeptId + "%'");
		}
		tSql.append(" and det.GS_ORDERRETURN_SIGN in ('"+GSBaseConstants.APEP_RETURN_SIGN_SUCCESS+"','-1') ");
		if(aGSOrderVO.orderCreateDateStr != null && aGSOrderVO.orderCreateDateStr.trim().length() !=0) {
			tSql.append(" and ord.GS_ORDER_CREATE_DATE >='" + aGSOrderVO.orderCreateDateStr.substring(0,4)+"-"+aGSOrderVO.orderCreateDateStr.substring(4,6)+"-"+aGSOrderVO.orderCreateDateStr.substring(6,8) + "'");
		}
		if(aGSOrderVO.orderCreateDateEnd != null && aGSOrderVO.orderCreateDateEnd.trim().length() !=0) {
			tSql.append(" and ord.GS_ORDER_CREATE_DATE <='" + aGSOrderVO.orderCreateDateEnd.substring(0,4)+"-"+aGSOrderVO.orderCreateDateEnd.substring(4,6)+"-"+aGSOrderVO.orderCreateDateEnd.substring(6,8) + " 23:59:59'");
		}
		if(aGSOrderVO.createUserCode != null && aGSOrderVO.createUserCode.trim().length() !=0 && !aGSOrderVO.createUserCode.trim().equals("0")){
			tSql.append(" and ord.gs_recorder_id = '"+ aGSOrderVO.createUserCode.trim()+"' ");
		}
		tSql.append(" and det.gs_provider_id!='90001' ");
	
		tSql.append(" order by ord.GS_ORDER_TEMPID");
		GSLogWriter.debug(this, sql + tSql.toString());
		try {
			tList = this.executeQuery(new GSOrderAnaDetailBean(), sql + tSql.toString(), null);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}
		return tList;
	}

	/**
	 * 根据订单编号查询订单及明细信息(for audit)
	 * @param aConn             DB连接
	 * @param aGSOrderVO        订单信息VO对象
	 * @return
	 * @throws GSException
	 */
	public ArrayList queryOrderAndDetail(Connection aConn,String orderId) throws GSException{

		GSOrderAuditVO tOrderVO = new GSOrderAuditVO();



		StringBuffer tSql = new StringBuffer("select B.GS_ORDER_TEMPID as P1,");
		tSql.append("B.GS_ORDER_DETAIL_ID as P4,B.GS_MANUFACTURE_ID as P5,C.GS_MANUFACTURE_NAME as P6,B.GS_ORDER_COUNT as P7,");
		tSql.append("B.GS_ORDER_DEPT_ID as P8 ,B.GS_CONCENTRICITY_ID as P9 ,B.GS_DELIVER_MODE as P10 ,B.GS_BALANCE_MODE as P11 ");
		tSql.append(" from GS_ORDER_DETAIL B,GS_MANUFACTURE C");
		tSql.append(" where B.GS_MANUFACTURE_ID=C.GS_MANUFACTURE_ID");


		ArrayList tList = null;
		if(orderId != null){
			tSql.append(" and B.GS_ORDER_TEMPID like '%" + orderId + "%'");
		}

		tSql.append("order by B.GS_ORDER_TEMPID");
		Object[] tObj = {};
		try{
			tList = this.executeQuery(aConn,tOrderVO,tSql.toString(),tObj);
		}catch(Exception e){
			GSLogWriter.error(this,e.toString());
			throw new GSException(GSException.E19999,GSException.E19999_MSG);
		}
		return tList;
	}


	/**
	 * 根据订单编号查询订单及明细信息(for authorize)
	 * @param aConn             DB连接
	 * @param aGSOrderVO        订单信息VO对象
	 * @return
	 * @throws GSException
	 */
	public ArrayList queryOrderAndDetailForAuthorize(Connection aConn,String orderId) throws GSException{

		GSOrderAuthorizeVO tOrderVO = new GSOrderAuthorizeVO();

		StringBuffer tSql = new StringBuffer("select B.GS_ORDER_DETAIL_ID as P1,B.GS_CONCENTRICITY_ID as P2,");
		tSql.append(" B.GS_MANUFACTURE_ID as P3,C.GS_MANUFACTURE_NAME as P4,B.GS_ORDER_COUNT as P5,");
		tSql.append(" B.GS_DELIVER_GOODS_DATE as P6,B.GS_DELIVER_GOODS_TIME as P7,");
		tSql.append(" B.GS_ORDER_DEPT_ID as P8 ,B.GS_DELIVER_GOODS_ADDR as P9,");
		tSql.append(" B.GS_ORDER_CONTACT_NAME as P10 ,B.GS_ORDER_CONTACT_PHONE as P11,");
		tSql.append(" B.GS_DELIVER_MODE as P12 ,B.GS_BALANCE_MODE as P13,B.GS_ORDER_TEMPID as P14 ");
		tSql.append(" from GS_ORDER_DETAIL B,GS_MANUFACTURE C ");
		tSql.append(" where B.GS_MANUFACTURE_ID=C.GS_MANUFACTURE_ID ");

		ArrayList tList = null;
		if(orderId != null){
			tSql.append(" and B.GS_ORDER_TEMPID like '%" + orderId + "%'");
		}

		Object[] tObj = {};
		try{
			tList = this.executeQuery(aConn,tOrderVO,tSql.toString(),tObj);
		}catch(Exception e){
			GSLogWriter.error(this,e.toString());
			throw new GSException(GSException.E19999,GSException.E19999_MSG);
		}
		return tList;
	}

	/**
	 * 根据订单编号查询订单
	 * 
	 * @param aOrderId
	 *            订单编号
	 * @return
	 * @throws GSException
	 */
	public GSOrderBean queryOrder(String aOrderId,Connection aConn) throws GSException {
		GSOrderBean tOrderBean = null;
		String tSql = "select * from GS_ORDER where GS_ORDER_TEMPID=?";
		Object[] tObj = { aOrderId };
		try {
			ArrayList tList = this.executeQuery(aConn,new GSOrderBean(), tSql, tObj);
			if (tList.size() > 0) {
				tOrderBean = (GSOrderBean) tList.get(0);
			}
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}
		return tOrderBean;
	}
	public GSOrderBean queryOrder(String aOrderId) throws GSException {
		GSOrderBean tOrderBean = null;
		String tSql = "select * from GS_ORDER where GS_ORDER_TEMPID=?";
		Object[] tObj = { aOrderId };
		try {
			ArrayList tList = this.executeQuery(new GSOrderBean(), tSql, tObj);
			if (tList.size() > 0) {
				tOrderBean = (GSOrderBean) tList.get(0);
			}
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}
		return tOrderBean;
	}

	/**
	 * 修改订单状态
	 * 
	 * @param aOrderId
	 *            订单编号
	 * @param aStatus
	 *            订单状态
	 * @param aOperId
	 *            操作员编号
	 * @param aConn
	 *            DB连接
	 * @return
	 * @throws GSException
	 */
	public boolean updateOrderStatus(String aOrderId, String aStatus, String aOperId, Connection aConn)
	throws GSException {
		String tSql = "update GS_ORDER set GS_ORDER_STATUS=?,GS_SUBMITER_ID1=?,GS_SUBMIT_DATE1=? where GS_ORDER_TEMPID=?";
		String tCurDT = DateUtil.getCurrentTime("DT", "AD");
		tCurDT = tCurDT.substring(0, 4) + "-" + tCurDT.substring(4, 6) + "-" + tCurDT.substring(6, 8) + " "
		+ tCurDT.substring(8, 10) + ":" + tCurDT.substring(10, 12) + ":" + tCurDT.substring(12, 14);
		Object tObj[] = { aStatus, aOperId, tCurDT, aOrderId };
		boolean tFlag = false;
		try {
			tFlag = this.executeUpdate(aConn, tSql, tObj);

		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19994, GSException.E19994_MSG);
		}
		return tFlag;
	}

	/**
	 * 初审订单
	 * @param index
	 * @param operId
	 * @param bool ture表示审核通过，false表示审核不通过
	 * @param conn
	 * @return
	 * @throws GSException
	 */
	public boolean firstAudit(String index ,String operId,boolean bool,String msg,Connection conn) throws GSException {
		String tSql1 = "update gs_order set gs_order_status =?, gs_firstaudit_date=?, gs_firstauditor_id=?,gs_firstaudit_advices=? where gs_order_tempid=?";
		String tSql2 = "update gs_order_detail set gs_order_detail_status =? where gs_order_tempid=? and gs_order_detail_status<>'21'";
		String firstAuditDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date(System.currentTimeMillis()));
		//TODO:1应修改为2
		Object[] tObj1 = {bool?"2":"4",firstAuditDate,operId,msg,index};
		Object[] tObj2 = {bool?"2":"4",index};
		boolean tFlag = false;
		try{
			tFlag = this.executeUpdate(conn,tSql1,tObj1);
			tFlag = this.executeUpdate(conn,tSql2,tObj2);
		}catch(Exception e){
			GSLogWriter.error(this,e.toString());
			throw new GSException(GSException.E20052,GSException.E20052_MSG);
		}
		return tFlag;
	}

	/**
	 * 复审订单
	 * @param index
	 * @param operId
	 * @param bool ture表示审核通过，false表示审核不通过
	 * @param conn
	 * @return
	 * @throws GSException
	 */
	public boolean review(String index ,String operId,boolean bool,boolean isSubmit,String msg,Connection conn) throws GSException {
		String tSql1 = "update gs_order set gs_order_status =?, gs_review_date=?, gs_reviewer_id=?,gs_review_advices=? where gs_order_tempid=?";
		String tSql2 = "update gs_order_detail set gs_order_detail_status =? where gs_order_tempid=? and gs_order_detail_status<>'21'";
		String firstAuditDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date(System.currentTimeMillis()));
		//TODO: 1应修改为3
		Object[] tObj1 = {bool?(isSubmit?"6":"3"):"5",firstAuditDate,operId,msg,index};
		Object[] tObj2 = {bool?(isSubmit?"6":"3"):"5",index};
		boolean tFlag = false;
		try{
			tFlag = this.executeUpdate(conn,tSql1,tObj1);
			tFlag = this.executeUpdate(conn,tSql2,tObj2);
		}catch(Exception e){
			GSLogWriter.error(this,e.toString());
			throw new GSException(GSException.E20053,GSException.E20053_MSG);
		}
		return tFlag;
	}

	/**
	 * 根据订单编号查询订单基本信息
	 * 
	 * @param aOrderTempID
	 *            订单编号
	 * @return GSOrderBean 订单基本信息
	 * @throws GSException
	 * @author Road Zheng
	 */
	public GSOrderBean getOrderByTempID(String aOrderTempID) throws GSException {
		GSOrderBean tResult = null;
		ArrayList tArrayList = null;
		try {
			StringBuffer tSql = new StringBuffer("select * ");
			tSql.append("from GS_ORDER ");
			tSql.append("where GS_ORDER_TEMPID = ?");
			GSOrderBean tGSOrderBean = new GSOrderBean();
			Object[] tObj = { aOrderTempID };
			tArrayList = this.executeQuery(tGSOrderBean, tSql.toString(), tObj);
		} catch (Exception sqle) {
			throw new GSException(GSException.E20043, GSException.E20043_MSG);
		}

		if (tArrayList != null && tArrayList.size() > 0) {
			tResult = (GSOrderBean) tArrayList.get(0);
		}
		return tResult;
	}

	/**
	 * 修改订单状态为取消
	 * 
	 * @param aOrderId
	 *            订单编号
	 * @param aStatus
	 *            订单状态
	 * @param aConn
	 *            DB连接
	 * @return
	 * @throws GSException
	 */
	public boolean updateOrderStatusToCancel(String aOrderId, String aOperatorLevel, Connection aConn)
	throws GSException {
		String tSql = "";
		if (aOperatorLevel.equals("1")) {
			tSql = "update GS_ORDER set GS_ORDER_STATUS=?,GS_ORDER_CREATE_DATE=? where GS_ORDER_TEMPID=?";
		} else if (aOperatorLevel.equals("2")) {
			tSql = "update GS_ORDER set GS_ORDER_STATUS=?,GS_FIRSTAUDIT_DATE=? where GS_ORDER_TEMPID=?";
		} else if (aOperatorLevel.equals("3")) {
			tSql = "update GS_ORDER set GS_ORDER_STATUS=?,GS_REVIEW_DATE=? where GS_ORDER_TEMPID=?";
		}
		String tCurDT = DateUtil.getCurrentTime("DT", "AD");
		tCurDT = tCurDT.substring(0, 4) + "-" + tCurDT.substring(4, 6) + "-" + tCurDT.substring(6, 8) + " "
		+ tCurDT.substring(8, 10) + ":" + tCurDT.substring(10, 12) + ":" + tCurDT.substring(12, 14);
		Object tObj[] = { "21", tCurDT, aOrderId };
		boolean tFlag = false;
		try {
			tFlag = this.executeUpdate(aConn, tSql, tObj);

		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19994, GSException.E19994_MSG);
		}
		return tFlag;
	}

	/**
	 * 修改订单状态到原先状态
	 * 
	 * @param aOrderId
	 *            订单编号
	 * @param aStatus
	 *            订单状态
	 * @param aConn
	 *            DB连接
	 * @return
	 * @throws GSException
	 */
	public boolean updateOrderStatusToBefore(String aOrderId, String aOperatorLevel, String aStatus, Connection aConn)
	throws GSException {
		String tSql = "";
		if (aOperatorLevel.equals("2")) {
			tSql = "update GS_ORDER set GS_ORDER_STATUS=?,GS_FIRSTAUDIT_DATE=? where GS_ORDER_TEMPID=?";
		} else if (aOperatorLevel.equals("3")) {
			tSql = "update GS_ORDER set GS_ORDER_STATUS=?,GS_REVIEW_DATE=? where GS_ORDER_TEMPID=?";
		}
		String tCurDT = DateUtil.getCurrentTime("DT", "AD");
		tCurDT = tCurDT.substring(0, 4) + "-" + tCurDT.substring(4, 6) + "-" + tCurDT.substring(6, 8) + " "
		+ tCurDT.substring(8, 10) + ":" + tCurDT.substring(10, 12) + ":" + tCurDT.substring(12, 14);
		Object tObj[] = { aStatus, tCurDT, aOrderId };
		boolean tFlag = false;
		try {
			tFlag = this.executeUpdate(aConn, tSql, tObj);

		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19994, GSException.E19994_MSG);
		}
		return tFlag;
	}

	/**
	 * 运营公司查询待提交订单及明细信息
	 * 
	 * @param aConn
	 *            DB连接
	 * @param aGSOrderVO
	 *            订单信息VO对象
	 * @param aOrg
	 *            可操作订货单位对象
	 * @return
	 * @throws GSException
	 */
	public ArrayList queryUncommitOrderAndDetail(Connection aConn, GSOrderSubmitVO aGSOrderVO, ArrayList aOrg) throws GSException {
		if(aOrg == null){
			aOrg =new ArrayList();
			String[] tTmpOrg ={"NoOrg"};
			aOrg.add(tTmpOrg);
		}
		GSOrderSubmitOfWorkingVO tOrderVO = new GSOrderSubmitOfWorkingVO();

		StringBuffer tSql = new StringBuffer("select distinct A.GS_ORDER_TEMPID as P1,A.GS_ORDER_CREATE_DATE as P2,");
		tSql.append("A.GS_ORDER_STATUS as P3,C.GS_MANUFACTURE_NAME as P4,B.GS_ORDER_COUNT as P5,");
		tSql.append("A.GS_WORKCORP_ID as P6 ,E.GS_ORG_NAME as P7,A.GS_RECORDER_ID as P8,");
		tSql.append("B.GS_CONCENTRICITY_ID as P9,A.GS_DELIVER_MODE as P10,A.GS_BALANCE_MODE as P11,C.GS_MANUFACTURE_SPEC as P12,");
		tSql.append("C.GS_MANUFACTURE_TYPE as P13,C.GS_MANUFACTURE_BRAND as P14,C.GS_QUANT_UNIT as P15,");
		tSql.append("A.GS_DELIVER_GOODS_ADDR as P16,A.GS_ORDER_CONTACT_NAME as P17,A.GS_ORDER_CONTACT_PHONE as P18,");
		tSql.append("B.GS_ORDER_DETAIL_ID as P19,B.GS_ORDER_DETAIL_STATUS as P20,b.gs_balance_mode as P21");
		tSql.append(" from GS_ORDER A,GS_ORDER_DETAIL B,GS_MANUFACTURE C,GS_ORDER_DEPT_INFO D,GS_ORGANIZATION E");
		tSql.append(" where A.GS_ORDER_TEMPID=B.GS_ORDER_TEMPID and B.GS_CONCENTRICITY_ID=C.GS_CONCENTRICITY_ID");
		tSql.append(" and (b.gs_manufacture_id is null or (b.gs_manufacture_id is not null and b.gs_manufacture_id = c.gs_manufacture_id))");
		tSql.append(" and B.GS_ORDER_DETAIL_STATUS='3'");
		tSql.append(" and A.GS_WORKCORP_ID=D.GS_ORG_ID and A.GS_WORKCORP_ID=E.GS_ORG_ID");
		tSql.append(" and A.GS_DELIVER_GOODS_ADDR = D.GS_DELIVER_GOODS_ADDR");

		ArrayList tList = null;
		if (aGSOrderVO.orderid != null) {
			tSql.append(" and A.GS_ORDER_TEMPID like '%" + aGSOrderVO.orderid + "%'");
		}
		if (aGSOrderVO.orderDeptId != null) {
			if (aGSOrderVO.orderDeptId.equals("0000")) {
				if (aOrg.size() > 0) {
					tSql.append(" and A.GS_WORKCORP_ID in(");
					for (int i = 0; i < aOrg.size(); i++) {
						String tStr[] = (String[]) aOrg.get(i);
						if (i == aOrg.size() - 1) {
							tSql.append("'" + tStr[0] + "')");
						} else {
							tSql.append("'" + tStr[0] + "',");
						}
					}
				}
			} else {
				tSql.append(" and A.GS_WORKCORP_ID ='" + aGSOrderVO.orderDeptId + "'");
			}
		}
		if (aGSOrderVO.deliverGoodsAddrId.equals("0000")) {
			tSql.append(" and D.GS_DELIVER_GOODS_ADDR_ID like '%'");
		} else {
			tSql.append(" and D.GS_DELIVER_GOODS_ADDR_ID ='" + aGSOrderVO.deliverGoodsAddrId + "'");
		}
		if (aGSOrderVO.concentricityId != null) {
			tSql.append(" and B.GS_CONCENTRICITY_ID like '" + aGSOrderVO.concentricityId + "%'");
		}
		if (aGSOrderVO.manufInfo != null) {
			tSql.append(" and (C.GS_MANUFACTURE_NAME like '%" + aGSOrderVO.manufInfo + "%'");
			tSql.append(" or C.GS_MANUFACTURE_TYPE like '%" + aGSOrderVO.manufInfo + "%'");
			tSql.append(" or C.GS_MANUFACTURE_SPEC like '%" + aGSOrderVO.manufInfo + "%'");
			tSql.append(" or C.GS_MANUFACTURE_BRAND like '%" + aGSOrderVO.manufInfo + "%'");
			tSql.append(" or C.GS_APPENDER_PROPERTY like '%" + aGSOrderVO.manufInfo + "%'");
			tSql.append(" or C.GS_MANUFACTURER_NAME like '%" + aGSOrderVO.manufInfo + "%')");
		}

		if(aGSOrderVO.orderCreateDateStr != null && aGSOrderVO.orderCreateDateStr.trim().length() !=0) {
			tSql.append(" and A.GS_ORDER_CREATE_DATE >='" + aGSOrderVO.orderCreateDateStr.substring(0,4)+"-"+aGSOrderVO.orderCreateDateStr.substring(4,6)+"-"+aGSOrderVO.orderCreateDateStr.substring(6,8) + "'");
		}
		if(aGSOrderVO.orderCreateDateEnd != null && aGSOrderVO.orderCreateDateEnd.trim().length() !=0) {
			tSql.append(" and A.GS_ORDER_CREATE_DATE <='" + aGSOrderVO.orderCreateDateEnd.substring(0,4)+"-"+aGSOrderVO.orderCreateDateEnd.substring(4,6)+"-"+aGSOrderVO.orderCreateDateEnd.substring(6,8) + " 23:59:59'");
		}
		//如果订单被明确拒绝，将不再显示===============
		tSql.append(" and b.GS_ORDERRETURN_SIGN  in ('"+GSBaseConstants.APEP_RETURN_SIGN_SUCCESS+"','-1')  ");
		//=========================================
		tSql.append(" order by A.GS_ORDER_CREATE_DATE desc");
		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tList = this.executeQuery(aConn, tOrderVO, tSql.toString(), tObj);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}
		return tList;
	}

	/**
	 * 查询取消订单详细列表
	 */
	public ArrayList queryCancelOrder(Connection aConn, GSOrderCancelVO aGSOrderCancelVO) throws GSException {
		StringBuffer tSql = new StringBuffer(GSOrderProcesser.aSql);
		ArrayList tList = null;
		if (aGSOrderCancelVO.orderid != null) {
			tSql.append(" and ord.GS_ORDER_TEMPID like '%" + aGSOrderCancelVO.orderid + "%'");
		}
		if (!aGSOrderCancelVO.orderDetailStatus.equals("00")) {
			tSql.append(" and det.GS_ORDER_DETAIL_STATUS = '" + aGSOrderCancelVO.orderDetailStatus + "'");
		} else {
			tSql.append(" and det.GS_ORDER_DETAIL_STATUS in('1','2','3','4','5','6','8','11') ");
		}
		if(aGSOrderCancelVO.orderDeptId!=null){
			tSql.append(" and ord.GS_WORKCORP_ID  like '" + aGSOrderCancelVO.orderDeptId + "%'");
		}
		if (aGSOrderCancelVO.concentricityId != null) {
			tSql.append(" and det.GS_CONCENTRICITY_ID like '" + aGSOrderCancelVO.concentricityId + "%'");
		}
		if (aGSOrderCancelVO.manufInfo != null) {
			tSql.append(" and (pro.NAME like '%" + aGSOrderCancelVO.manufInfo.trim() + "%'");
			tSql.append(" or pro.MODEL like '%" + aGSOrderCancelVO.manufInfo.trim() + "%'");
			tSql.append(" or pro.SPEC like '%" + aGSOrderCancelVO.manufInfo.trim() + "%'");
			tSql.append(" or pro.PRODUCT_BRAND like '%" + aGSOrderCancelVO.manufInfo.trim() + "%'");
			tSql.append(" or pro.APPENDER_PROPERTY like '%" + aGSOrderCancelVO.manufInfo.trim() + "%'");
			tSql.append(" or pro.MANUFATURER like '%" + aGSOrderCancelVO.manufInfo.trim() + "%')");
		}
		if (aGSOrderCancelVO.orderCreateDateStr != null && !"".equals(aGSOrderCancelVO.orderCreateDateStr)) {
			tSql.append(" and to_char(to_date(ord.GS_ORDER_CREATE_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD') >='" + aGSOrderCancelVO.orderCreateDateStr.replace('/', '-') + "'");
		}
		if (aGSOrderCancelVO.orderCreateDateEnd != null && !"".equals(aGSOrderCancelVO.orderCreateDateEnd)) {
			tSql.append(" and to_char(to_date(ord.GS_ORDER_CREATE_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD') <='" + aGSOrderCancelVO.orderCreateDateEnd.replace('/', '-') + "'");
		}
		//图件号
		if(aGSOrderCancelVO.drawingpiececode != null && !aGSOrderCancelVO.drawingpiececode.equals("")){
			tSql.append(" and pro.DRAWING_PIECE_CODE like '%" + aGSOrderCancelVO.drawingpiececode.trim() + "%'");
		}
		tSql.append(" order by det.GS_LASTMODIFY_DATE desc");
		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tList = this.executeQuery(aConn, new GSOrderAnaDetailBean(), tSql.toString(), tObj);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}
		return tList;
	}

	/**
	 * 执行订单明细的取消状态
	 * @return  boolean
	 * @throws  GSException
	 */
	public boolean modifyCancelOrder(String aOrderDetailStatus,String aOrderDetailId,
			String aOperId,String aOperDate)throws GSException{

		String tSql= "update GS_ORDER_DETAIL set GS_ORDER_DETAIL_STATUS = ?,GS_LASTMODIFER_ID=?," +
		"GS_LASTMODIFY_DATE=? where GS_ORDER_DETAIL_ID = ? ";
		Object tObj[] = new Object[4];
		tObj[0] = aOrderDetailStatus;
		tObj[1] = aOperId;
		tObj[2] = aOperDate;
		tObj[3] = aOrderDetailId;

		boolean tResult = false;
		try{
			tResult = executeUpdate(tSql,tObj);
		}catch(Exception ex){
			GSLogWriter.error(this,ex.getMessage());
			throw new GSException(ex.getMessage());
		}
		return tResult;
	}

	/**
	 * 获取网上订单收货跟踪信息
	 * @param aConn             数据库链接
	 * @param aForm             Form对象
	 * @param aOrg              组织对象
	 * @param pageNo            页号
	 * @param pageSize          页大小
	 * @return
	 * @throws GSException
	 */
	public ArrayList getOrderDeliverStatusList(Connection aConn, GSOrderStatusTraceQueryForm aForm,ArrayList aOrg,int pageNo,int pageSize) throws GSException {
		GSOrderDeliverStatusVO tOrderVO = new GSOrderDeliverStatusVO();
		ArrayList tList = null;
		StringBuffer tSql = new StringBuffer(getOrderDeliverStatusSql(aForm,aOrg));
		tSql.append(" order by god.GS_LASTMODIFY_DATE desc");
		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tList = this.executeQuery(aConn, tOrderVO, tSql.toString(), tObj,pageNo,pageSize);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}

		return tList;
	}
	/**
	 * 获取网上订单收货跟踪信息总笔数
	 * @param aForm             Form对象
	 * @param aOrg              组织对象
	 * @return
	 * @throws GSException
	 */
	public String[] getOrderDeliverStatuslstCount(GSOrderStatusTraceQueryForm aForm,ArrayList aOrg) throws GSException{
		String[] tStr = new String[2];
		ArrayList tList = new ArrayList();
		StringBuffer tSql = new StringBuffer("select count(*) as S1,sum(P8*P11) as S2 from(");
		tSql.append(getOrderDeliverStatusSql(aForm,aOrg));
		tSql.append(")");
		Object[] tObj = {};
		try{
			tList = this.executeQuery(new GSOrderStatusTraceQryVO(),tSql.toString(),tObj);
			if(tList.size() > 0){
				GSOrderStatusTraceQryVO tVO = (GSOrderStatusTraceQryVO)tList.get(0);
				tStr[0] = String.valueOf(tVO.count);
				tStr[1] = StringUtils.formatEDouble(tVO.totalAmt,4);
			}else{
				tStr[0] = "0";
				tStr[1] = "0";
			}
		}catch(Exception e){
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}
		return tStr;
	}
	/**
	 * 获取网上订单收货跟踪信息查询SQL
	 * @param aForm             Form对象
	 * @param aOrg              组织对象
	 * @return
	 */
	private String getOrderDeliverStatusSql(GSOrderStatusTraceQueryForm aForm,ArrayList aOrg){

		if(aOrg == null){
			aOrg =new ArrayList();
			String[] tTmpOrg ={"NoOrg"};
			aOrg.add(tTmpOrg);
		}

		String tOrderid = aForm.getOrderid();
		String tProviderName = aForm.getProviderName();
		String tDeliverGoodsAddrId = aForm.getDeliverGoodsAddrId();
		String tConcentricityId = aForm.getConcentricityId();
		String tManufInfo = aForm.getManufInfo();
		String tManuftype1 = aForm.getManuftype1();
		String tManuftype2 = aForm.getManuftype2();
		String tManuftype3 = aForm.getManuftype3();
		String tOrderCreateDateStr = aForm.getOrderCreateDateStr();
		String tOrderCreateDateEnd = aForm.getOrderCreateDateEnd();
		StringBuffer tSql = new StringBuffer("Select distinct god.gs_order_detail_id as P1,god.gs_concentricity_id as P2,gm.gs_manufacture_name as P3 ,gm.gs_is_tradeflat_manufacture as P4, ");
		tSql.append(" gm.gs_manufacture_spec as P5,gm.gs_manufacture_type as P6,gm.gs_manufacture_brand as P7,god.gs_order_count as P8,gp.GS_PROVIDER_SHORTNAME as P9, ");
		tSql.append(" gorg.gs_org_name as P10,gm.gs_manufacture_unitprice as P11,god.gs_deliver_goods_date as P12,god.gs_deliver_goods_time as P13,god.gs_provider_id As P14,god.gs_order_tempid As P15, ");
		tSql.append(" gm.gs_quant_unit As P16,gp.GS_IS_TRADEFLAT_PROVIDER as P17,god.GS_LASTMODIFY_DATE as P18,god.gs_balance_mode as P19 ");
		tSql.append(" From gs_order_detail god,gs_manufacture gm,gs_provider gp,gs_order gord,gs_order_dept_info godi,gs_organization gorg,");
		tSql.append(" GS_MANUFACTURE_CATEGORY g ");
		tSql.append(" Where god.gs_order_detail_status = '9'");
		tSql.append(" And gp.GS_IS_TRADEFLAT_PROVIDER = '0' ");
		tSql.append(" And god.gs_manufacture_id = gm.gs_manufacture_id");
		tSql.append(" And god.gs_provider_id = gp.gs_provider_id ");
		tSql.append(" And god.gs_order_tempid = gord.gs_order_tempid ");
		tSql.append(" And gord.gs_workcorp_id = godi.gs_org_id ");
		tSql.append(" And gord.gs_workcorp_id = gorg.gs_org_id ");
		tSql.append(" and gm.GS_MANUFACTURE_CATEGORY_ID = g.GS_MANUFACTURE_CATEGORY_ID");
		if (tOrderid != null && tOrderid.trim().length() > 0) {
			tSql.append(" And god.gs_order_detail_id like '%" + tOrderid.trim() + "%' ");
		}
		if (tProviderName != null && tProviderName.trim().length() > 0) {
			tSql.append(" And (gp.gs_provider_name like '%" + tProviderName.trim() + "%' ");
			tSql.append(" or gp.GS_PROVIDER_SHORTNAME like '%" + tProviderName.trim() + "%')");
		}
		if (tDeliverGoodsAddrId != null && tDeliverGoodsAddrId.length() > 0 && !tDeliverGoodsAddrId.equals("0000")) {
			tSql.append(" And godi.gs_deliver_goods_addr_id = '" + tDeliverGoodsAddrId + "' ");
		}
		if (tConcentricityId != null && tConcentricityId.trim().length() > 0) {
			tSql.append(" And god.gs_concentricity_id like '%" + tConcentricityId.trim() + "%' ");
		}
		if(tOrderCreateDateStr != null && tOrderCreateDateStr.trim().length() != 0) {
			tSql.append(" and gord.GS_ORDER_CREATE_DATE >='" + tOrderCreateDateStr.substring(0,4)+"-"+tOrderCreateDateStr.substring(4,6)+"-"+tOrderCreateDateStr.substring(6,8) + "'");
		}
		if(tOrderCreateDateEnd != null && tOrderCreateDateEnd.trim().length() != 0) {
			tSql.append(" and gord.GS_ORDER_CREATE_DATE <='" + tOrderCreateDateEnd.substring(0,4)+"-"+tOrderCreateDateEnd.substring(4,6)+"-"+tOrderCreateDateEnd.substring(6,8) + " 23:59:59'");
		}

		if (tManufInfo != null && tManufInfo.length() > 0) {
			tSql.append(" and (gm.GS_MANUFACTURE_NAME like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURE_TYPE like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURE_SPEC like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURE_BRAND like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_APPENDER_PROPERTY like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURER_NAME like '%" + tManufInfo + "%')");
		}

		if (tManuftype1 != null && tManuftype1.length() > 0) {
			if(tManuftype1.equals("0")){
				tSql.append(" and g.GS_MANUFACTURE_CATEGORY_ID like '%'");
			}else if(tManuftype2.equals("0")){
				tSql.append(" and (g.GS_MANUFACTURE_CATEGORY_ID1 ='" + tManuftype1 + "'");
				tSql.append(" or g.GS_MANUFACTURE_CATEGORY_ID='" + tManuftype1 + "')");
			}else if(tManuftype3.equals("0")){
				tSql.append(" and (g.GS_MANUFACTURE_CATEGORY_ID2 ='" + tManuftype2 + "'");
				tSql.append(" or g.GS_MANUFACTURE_CATEGORY_ID='" + tManuftype2 + "')");
			}else{
				tSql.append(" and g.GS_MANUFACTURE_CATEGORY_ID ='" + tManuftype3 + "'");
			}
		}

		if(aForm.getOrderDeptId() != null && !aForm.getOrderDeptId().equals("0000")){
			tSql.append(" and gord.GS_WORKCORP_ID ='" + aForm.getOrderDeptId() + "'");
		}else{
			if(aOrg != null && aOrg.size() > 0){
				tSql.append(" and gord.GS_WORKCORP_ID in(");
				for(int i=0; i<aOrg.size();i++){
					String tStr[] = (String[])aOrg.get(i);
					if( i == aOrg.size()-1){
						tSql.append("'"+tStr[0]+"')");
					}else{
						tSql.append("'"+tStr[0]+"',");
					}
				}
			}
		}
		return tSql.toString();
	}
	/**
	 * 查询网上订单送货跟踪信息
	 * @param aConn             DB连接
	 * @param aForm             Form对象
	 * @param aOrg              组织对象
	 * @param pageNo            页号
	 * @param pageSize          页大小
	 * @return
	 * @throws GSException
	 */
	public ArrayList getOrderReceiveStatusList(Connection aConn, GSOrderStatusTraceQueryForm aForm,ArrayList aOrg,int pageNo, int pageSize) throws GSException {
		GSOrderDeliverStatusVO tOrderVO = new GSOrderDeliverStatusVO();
		StringBuffer tSql = new StringBuffer();
		tSql.append(getOrderReceiveStatusLstSql(aForm,aOrg));
		tSql.append(" order by god.GS_LASTMODIFY_DATE desc");
		ArrayList tList = null;

		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tList = this.executeQuery(aConn, tOrderVO, tSql.toString(), tObj,pageNo,pageSize);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}

		return tList;
	}
	/**
	 * 查询网上订单送货跟踪信息总笔数
	 * @param aForm             Form对象
	 * @param aOrg              组织对象
	 * @return
	 * @throws GSException
	 */
	public String[] getOrderRecStatuslstCount(GSOrderStatusTraceQueryForm aForm,ArrayList aOrg) throws GSException{
		int tSize = 0;
		String[] tStr = new String[2];
		ArrayList tList = new ArrayList();
		StringBuffer tSql = new StringBuffer("select count(*) as S1,sum(P8 * P11) as S2 from(");
		tSql.append(getOrderReceiveStatusLstSql(aForm,aOrg));
		tSql.append(")");
		GSLogWriter.debug(this,tSql.toString());
		Object[] tObj = {};
		try{
			tList = this.executeQuery(new GSOrderStatusTraceQryVO(),tSql.toString(),tObj);
			if(tList.size() > 0){
				GSOrderStatusTraceQryVO tVO = (GSOrderStatusTraceQryVO)tList.get(0);
				tStr[0] = String.valueOf(tVO.count);
				tStr[1] = String.valueOf(tVO.totalAmt);
			}else{
				tStr[0] = "0";
				tStr[1] = "0";
			}
		}catch(Exception e){
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}
		return tStr;
	}
	/**
	 * 取得网上订单送货跟踪查询SQL
	 * @param aForm             Form对象
	 * @param aOrg              组织对象
	 * @return
	 * @throws GSException
	 */
	private String getOrderReceiveStatusLstSql(GSOrderStatusTraceQueryForm aForm,ArrayList aOrg) throws GSException {
		if(aOrg == null){
			aOrg =new ArrayList();
			String[] tTmpOrg ={"NoOrg"};
			aOrg.add(tTmpOrg);
		}
		String tOrderid = aForm.getOrderid();
		String tProviderName = aForm.getProviderName();
		String tDeliverGoodsAddrId = aForm.getDeliverGoodsAddrId();
		String tConcentricityId = aForm.getConcentricityId();
		String tManufInfo = aForm.getManufInfo();
		String tManuftype1 = aForm.getManuftype1();
		String tManuftype2 = aForm.getManuftype2();
		String tManuftype3 = aForm.getManuftype3();
		String tOrderCreateDateStr = aForm.getOrderCreateDateStr();
		String tOrderCreateDateEnd = aForm.getOrderCreateDateEnd();
		StringBuffer tSql = new StringBuffer("Select distinct god.gs_order_detail_id as P1,god.gs_concentricity_id as P2,gm.gs_manufacture_name as P3 ,gm.gs_is_tradeflat_manufacture as P4, ");
		tSql.append(" gm.gs_manufacture_spec as P5,gm.gs_manufacture_type as P6,gm.gs_manufacture_brand as P7,god.gs_order_count as P8,gp.GS_PROVIDER_SHORTNAME as P9, ");
		tSql.append(" gorg.gs_org_name as P10,gm.gs_manufacture_unitprice as P11,god.gs_deliver_goods_date as P12,god.gs_deliver_goods_time as P13,god.gs_provider_id As P14,god.gs_order_tempid As P15, ");
		tSql.append(" gm.gs_quant_unit As P16,gp.GS_IS_TRADEFLAT_PROVIDER as P17,god.GS_LASTMODIFY_DATE as P18,god.gs_balance_mode as P19 ");
		tSql.append(" From gs_order_detail god,gs_manufacture gm,gs_provider gp,gs_order gord,gs_order_dept_info godi,gs_organization gorg,");
		tSql.append(" GS_MANUFACTURE_CATEGORY g");
		tSql.append(" Where god.gs_order_detail_status = '10'");
		tSql.append(" And god.gs_order_tempid = gord.gs_order_tempid ");
		tSql.append(" And gp.GS_IS_TRADEFLAT_PROVIDER = '0'");
		tSql.append(" And god.gs_provider_id = gp.gs_provider_id ");
		tSql.append(" And gord.gs_workcorp_id = godi.gs_org_id ");
		tSql.append(" And gord.gs_workcorp_id = gorg.gs_org_id ");
		tSql.append(" And god.gs_manufacture_id = gm.gs_manufacture_id ");
		tSql.append(" AND gm.gs_manufacture_category_id = g.gs_manufacture_category_id");
		if (tOrderid != null && tOrderid.trim().length() > 0) {
			tSql.append(" And god.gs_order_detail_id like '%" + tOrderid.trim() + "%' ");
		}
		if (tProviderName != null && tProviderName.trim().length() > 0) {
			tSql.append(" And (gp.gs_provider_name like '%" + tProviderName.trim() + "%' ");
			tSql.append(" or gp.GS_PROVIDER_SHORTNAME like '%" + tProviderName.trim() + "%')");
		}
		if (tDeliverGoodsAddrId != null && tDeliverGoodsAddrId.length() > 0 && !tDeliverGoodsAddrId.equals("0000")) {
			tSql.append(" And godi.gs_deliver_goods_addr_id = '" + tDeliverGoodsAddrId + "' ");
		}
		if (tConcentricityId != null && tConcentricityId.trim().length() > 0) {
			tSql.append(" And god.gs_concentricity_id like '%" + tConcentricityId.trim() + "%' ");
		}
		if(tOrderCreateDateStr != null  && tOrderCreateDateStr.trim().length() != 0) {
			tSql.append(" and gord.GS_ORDER_CREATE_DATE >='" + tOrderCreateDateStr.substring(0,4)+"-"+tOrderCreateDateStr.substring(4,6)+"-"+tOrderCreateDateStr.substring(6,8) + "'");
		}
		if(tOrderCreateDateEnd != null  && tOrderCreateDateEnd.trim().length() != 0) {
			tSql.append(" and gord.GS_ORDER_CREATE_DATE <='" + tOrderCreateDateEnd.substring(0,4)+"-"+tOrderCreateDateEnd.substring(4,6)+"-"+tOrderCreateDateEnd.substring(6,8) + " 23:59:59'");
		}

		if (tManufInfo != null && tManufInfo.length() > 0) {
			tSql.append(" and (gm.GS_MANUFACTURE_NAME like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURE_TYPE like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURE_SPEC like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURE_BRAND like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_APPENDER_PROPERTY like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURER_NAME like '%" + tManufInfo + "%')");
		}

		if (tManuftype1 != null && tManuftype1.length() > 0) {
			if(tManuftype1.equals("0")){
				tSql.append(" and g.GS_MANUFACTURE_CATEGORY_ID like '%'");
			}else if(tManuftype2.equals("0")){
				tSql.append(" and (g.GS_MANUFACTURE_CATEGORY_ID1 ='" + tManuftype1 + "'");
				tSql.append(" or g.GS_MANUFACTURE_CATEGORY_ID='" + tManuftype1 + "')");
			}else if(tManuftype3.equals("0")){
				tSql.append(" and (g.GS_MANUFACTURE_CATEGORY_ID2 ='" + tManuftype2 + "'");
				tSql.append(" or g.GS_MANUFACTURE_CATEGORY_ID='" + tManuftype2 + "')");
			}else{
				tSql.append(" and g.GS_MANUFACTURE_CATEGORY_ID ='" + tManuftype3 + "'");
			}
		}

		if(aForm.getOrderDeptId() != null && !aForm.getOrderDeptId().equals("0000")){
			tSql.append(" and gord.GS_WORKCORP_ID ='" + aForm.getOrderDeptId() + "'");
		}else{
			if(aOrg != null && aOrg.size() > 0){
				tSql.append(" and gord.GS_WORKCORP_ID in(");
				for(int i=0; i<aOrg.size();i++){
					String tStr[] = (String[])aOrg.get(i);
					if( i == aOrg.size()-1){
						tSql.append("'"+tStr[0]+"')");
					}else{
						tSql.append("'"+tStr[0]+"',");
					}
				}
			}
		}
		return tSql.toString();
	}
	public ArrayList getOrderDetailListOfBusQry(Connection aConn, GSOrderDetailQueryForm aForm,int pageNo,int pageSize) throws GSException {
		GSOrderDetailQueryVO tOrderVO = new GSOrderDetailQueryVO();
		StringBuffer tSql = new StringBuffer(getOrderDetailListSqlOfBusQry(aForm));
		tSql.append(" order by god.GS_LASTMODIFY_DATE desc");
		ArrayList tList = null;
		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tList = this.executeQuery(aConn, tOrderVO, tSql.toString(), tObj,pageNo,pageSize);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}

		return tList;
	}

	/**
	 * 结算审核的查询方法
	 * @param aForm
	 * @param aOrg
	 * @return
	 * @throws GSException
	 */
	private String getOrderDetailListSqlOfBusQryForBalanceAudit(GSOrderDetailQueryForm aForm,ArrayList aOrg)throws GSException{
		if(aOrg == null){
			aOrg =new ArrayList();
			String[] tTmpOrg ={"NoOrg"};
			aOrg.add(tTmpOrg);
		}
		String tOrderid = aForm.getOrderid();
		String tProviderName = aForm.getProviderName();
		String tDeliverGoodsAddrId = aForm.getDeliverGoodsAddrId();
		String tConcentricityId = aForm.getConcentricityId();
		String tManufInfo = aForm.getManufInfo();
		String tManuftype1 = aForm.getManuftype1();
		String tManuftype2 = aForm.getManuftype2();
		String tManuftype3 = aForm.getManuftype3();
		String tOrderCreateDateStr = aForm.getOrderCreateDateStr();
		String tOrderCreateDateEnd = aForm.getOrderCreateDateEnd();
		String tOrderDetailStatus = aForm.getOrderDetailStatus();
		String tOrderType = aForm.getOrderType();
		String tDeliverGoodsId = aForm.getDeliverGoodsId();

		// by sindy start 070821
		SimpleDateFormat tm = new SimpleDateFormat("yyyy-MM-dd");
		String strSysDate = tm.format(new Date(System.currentTimeMillis()));

		GSBalanceParamDAO dao = new GSBalanceParamDAO();
		GSBalanceParamBean tBean = new GSBalanceParamBean();
		tBean = dao.queryBalanceDate();

		String strEnd = tBean.getGs_balance_end_date();

		if (strEnd.length()==1) {
			strEnd = "0" + strEnd;
		}

		if (tBean.getGs_balance_date_type().equals("1"))
		{
			strEnd = DateUtil.getMaxMonthDay(strSysDate) + "";
		}

		String strFinanceEndDate = strSysDate.substring(0,8) + strEnd + " 23:59:59";

		// by sindy end

		GSProviderDAO pDAO = new GSProviderDAO();
		GSProviderBean pBean = pDAO.queryProvider(GSSystemConfigurator.getParam("gs.provider.cczxid"));

		StringBuffer tSql = new StringBuffer(
		"Select  Distinct god.gs_order_detail_id as P1,god.gs_concentricity_id as P2,gm.gs_manufacture_name as P3 ,gm.gs_is_tradeflat_manufacture as P4, ");
		tSql.append(" gm.gs_manufacture_spec as P5,gm.gs_manufacture_type as P6,gm.gs_manufacture_brand as P7,god.GS_SIGNIN_COUNT as P8,gp.GS_PROVIDER_SHORTNAME as P9, ");
		tSql.append(" gorg.gs_org_name as P10,god.gs_goods_unitprice as P11,god.gs_deliver_goods_date as P12,god.gs_deliver_goods_time as P13,god.gs_provider_id As P14,god.gs_order_tempid As P15, ");
		tSql.append(" God.Gs_Order_Detail_Status As P16, Gord.Gs_Order_Create_Date As P17,");
		tSql.append(" gm.gs_quant_unit As P18,god.GS_LASTMODIFY_DATE as P19,gp.GS_IS_TRADEFLAT_PROVIDER as P20,god.GS_LASTMODIFY_DATE as P21,god.gs_balance_mode as P24 ");
		tSql.append(" From gs_order_detail god,gs_manufacture gm,gs_provider gp,gs_order gord,gs_order_dept_info godi,gs_organization gorg,");
		tSql.append(" GS_MANUFACTURE_CATEGORY g");
		tSql.append(" Where (god.gs_is_balanceed = 0 or god.gs_is_prov_balanceed = 0) ");//未订货单位结算或未供应商结算

		// by sindy start 070821
		tSql.append(" and to_char(to_date(gord.GS_ORDER_CREATE_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD HH24:mi:ss') <='"
				+ strFinanceEndDate + "'"); //加入结算日期的判断
		// by sindy end

		tSql.append("and gord.GS_BALANCE_MODE <> '203' And god.gs_order_tempid = gord.gs_order_tempid ");
		tSql.append(" And god.gs_provider_id = gp.gs_provider_id ");
		tSql.append(" And god.gs_manufacture_id = gm.gs_manufacture_id ");
		tSql.append(" And gord.gs_workcorp_id = godi.gs_org_id ");
		tSql.append(" And gord.GS_DELIVER_GOODS_ADDR = godi.GS_DELIVER_GOODS_ADDR ");
		tSql.append(" And gord.gs_workcorp_id = gorg.gs_org_id ");
		tSql.append(" AND gm.gs_manufacture_category_id = g.gs_manufacture_category_id");
		if (tOrderid != null && tOrderid.trim().length() > 0) {
			tSql.append(" And god.gs_order_detail_id like '%" + tOrderid.trim() + "%' ");
		}
		if (tProviderName != null && tProviderName.trim().length() > 0) {
			//2006-10-23 如果要查询仓储中心，则给他查询所有平台特约产品，否则，按正常情况查询
			if(tProviderName.equals(pBean.getGs_provider_shortname()) || tProviderName.equals(pBean.getGs_provider_name())){
				tSql.append(" And gm.GS_IS_TRADEFLAT_MANUFACTURE = '0' ");
			}else{
				tSql.append(" And (gp.gs_provider_name like '%" + tProviderName.trim() + "%' ");
				tSql.append(" or gp.gs_provider_shortname like '%" + tProviderName.trim() + "%')");
			}
		}

		if (tDeliverGoodsAddrId != null && tDeliverGoodsAddrId.length() > 0 && !tDeliverGoodsAddrId.equals("0000")) {
			tSql.append(" And godi.gs_deliver_goods_addr_id = '" + tDeliverGoodsAddrId + "' ");
		}
		if (tConcentricityId != null && tConcentricityId.trim().length() > 0) {
			tSql.append(" And god.gs_concentricity_id like '%" + tConcentricityId.trim() + "%' ");
		}
		if(tOrderCreateDateStr != null  && tOrderCreateDateStr.trim().length() != 0) {
			tSql.append(" and gord.GS_ORDER_CREATE_DATE >='" + tOrderCreateDateStr.substring(0,4)+"-"+tOrderCreateDateStr.substring(4,6)+"-"+tOrderCreateDateStr.substring(6,8) + "'");
		}
		if(tOrderCreateDateEnd != null  && tOrderCreateDateEnd.trim().length() != 0) {
			tSql.append(" and to_char(to_date(gord.GS_ORDER_CREATE_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD') <='" + tOrderCreateDateEnd.replace('/','-') + "'");
		}
		if (tManufInfo != null && tManufInfo.length() > 0) {
			tSql.append(" and (gm.GS_MANUFACTURE_NAME like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURE_TYPE like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURE_SPEC like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURE_BRAND like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_APPENDER_PROPERTY like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURER_NAME like '%" + tManufInfo + "%')");
		}

		if (tManuftype1 != null && tManuftype1.length() > 0) {
			if(tManuftype1.equals("0")){
				tSql.append(" and g.GS_MANUFACTURE_CATEGORY_ID like '%'");
			}else if(tManuftype2.equals("0")){
				tSql.append(" and (g.GS_MANUFACTURE_CATEGORY_ID1 ='" + tManuftype1 + "'");
				tSql.append(" or g.GS_MANUFACTURE_CATEGORY_ID='" + tManuftype1 + "')");
			}else if(tManuftype3.equals("0")){
				tSql.append(" and (g.GS_MANUFACTURE_CATEGORY_ID2 ='" + tManuftype2 + "'");
				tSql.append(" or g.GS_MANUFACTURE_CATEGORY_ID='" + tManuftype2 + "')");
			}else{
				tSql.append(" and g.GS_MANUFACTURE_CATEGORY_ID ='" + tManuftype3 + "'");
			}
		}

		//使用12作为唯一状态
		tSql.append(" And god.gs_order_detail_status = '12' ");
		if (tOrderType != null && tOrderType.length() > 0 && !tOrderType.equals("0000")) {
			tSql.append(" And gord.gs_order_category = '" + tOrderType + "' ");
		}
		if (tDeliverGoodsId != null && tDeliverGoodsId.trim().length() > 0) {
			//tSql.append(" And god.gs_deliver_goods_id like '%" + tDeliverGoodsId.trim() + "%' ");
			//20060802 by simonyi 送货单号完全匹配查询
			tSql.append(" And god.gs_deliver_goods_id = '" + tDeliverGoodsId.trim() + "' ");
		}

		if(aForm.getOrderDeptId() != null && !aForm.getOrderDeptId().equals("0000")){
			tSql.append(" and gord.GS_WORKCORP_ID ='" + aForm.getOrderDeptId() + "'");
		}else{
			if(aOrg != null && aOrg.size() > 0){
				tSql.append(" and gord.GS_WORKCORP_ID in(");
				for(int i=0; i<aOrg.size();i++){
					String tStr[] = (String[])aOrg.get(i);
					if( i == aOrg.size()-1){
						tSql.append("'"+tStr[0]+"')");
					}else{
						tSql.append("'"+tStr[0]+"',");
					}
				}
			}
		}
		//以订单审核纳入结算只显示买断结算
		tSql.append(" and god.gs_balance_mode='0'");
		return tSql.toString();

	}
	private String getOrderDetailListSqlOfBusQry(GSOrderDetailQueryForm aForm)throws GSException{
		String tOrderid = aForm.getOrderid();
		String tProviderName = aForm.getProviderName();
		String tConcentricityId = aForm.getConcentricityId();
		String tManufInfo = aForm.getManufInfo();
		String tManuftype1 = aForm.getManuftype1();
		String tManuftype2 = aForm.getManuftype2();
		String tManuftype3 = aForm.getManuftype3();
		String tOrderCreateDateStr = aForm.getOrderCreateDateStr();
		String tOrderCreateDateEnd = aForm.getOrderCreateDateEnd();
		String tOrderDetailStatus = aForm.getOrderDetailStatus();
		String tOrderType = aForm.getOrderType();
		String tDeliverGoodsId = aForm.getDeliverGoodsId();
		String drawingpiececode = aForm.getDrawingpiececode();
		String allstatus=aForm.getAllstatus();

		StringBuffer tSql = new StringBuffer(GSOrderDetailDAO.getOrderAndDetailAndCategorySql());

		if (tOrderid != null && tOrderid.trim().length() > 0) {
			tSql.append(" And det.gs_order_detail_id like '%" + tOrderid.trim() + "%' ");
		}
		if (tProviderName != null && tProviderName.trim().length() > 0) {
			tSql.append(" And (prv.name like '%" + tProviderName.trim() + "%' ");
			tSql.append(" or prv.FULL_NAME like '%" + tProviderName.trim() + "%' or det.gs_provider_id='"+tProviderName.trim()+"' )");
		}
		if (tConcentricityId != null && tConcentricityId.trim().length() > 0) {
			tSql.append(" And det.gs_concentricity_id like '%" + tConcentricityId.trim() + "%' ");
		}
		if(tOrderCreateDateStr != null  && tOrderCreateDateStr.trim().length() != 0) {
			tSql.append(" and ord.GS_ORDER_CREATE_DATE >='" + tOrderCreateDateStr.substring(0,4)+"-"+tOrderCreateDateStr.substring(4,6)+"-"+tOrderCreateDateStr.substring(6,8) + "'");
		}
		if(tOrderCreateDateEnd != null  && tOrderCreateDateEnd.trim().length() != 0) {
			tSql.append(" and ord.GS_ORDER_CREATE_DATE <='" + tOrderCreateDateEnd.substring(0,4)+"-"+tOrderCreateDateEnd.substring(4,6)+ "-"+tOrderCreateDateEnd.substring(6,8) + " 23:59:59'");
		}
		if (tManufInfo != null && tManufInfo.length() > 0) {
			tSql.append(" and (pro.NAME like '%" + tManufInfo + "%'");
			tSql.append(" or pro.MODEL like '%" + tManufInfo + "%'");
			tSql.append(" or pro.SPEC like '%" + tManufInfo + "%'");
			tSql.append(" or pro.PRODUCT_BRAND like '%" + tManufInfo + "%'");
			tSql.append(" or pro.APPENDER_PROPERTY like '%" + tManufInfo + "%'");
			tSql.append(" or pro.MANUFATURER like '%" + tManufInfo + "%')");
		}

		if (tManuftype1 != null && tManuftype1.length() > 0) {
			if(tManuftype1.equals("0")){
				tSql.append(" and cat.CODE like '%'");
			}else if(tManuftype2.equals("0")){
				tSql.append(" and (cat.CATEGORY_ID1 ='" + tManuftype1 + "'");
				tSql.append(" or cat.CODE='" + tManuftype1 + "')");
			}else if(tManuftype3.equals("0")){
				tSql.append(" and (cat.CATEGORY_ID2 ='" + tManuftype2 + "'");
				tSql.append(" or cat.code='" + tManuftype2 + "')");
			}else{
				tSql.append(" and cat.code ='" + tManuftype3 + "'");
			}
		}
		if (tOrderDetailStatus != null && tOrderDetailStatus.length() > 0 && !tOrderDetailStatus.equals("0000")) {
			tSql.append(" And det.gs_order_detail_status = '" + tOrderDetailStatus + "' ");
		}else{
			tSql.append(" and (det.GS_ORDER_DETAIL_STATUS in ('3','6','7','8','9','10','11','12','13','14','18','19','20','21','22')) ");
			if(allstatus!=null&&"1".equals(allstatus)){
				tSql.append(" and (det.GS_ORDER_DETAIL_STATUS in ('9','10','12')) ");
			}
		}
		
		if (tOrderType != null && tOrderType.length() > 0 && !tOrderType.equals("0000")) {
			tSql.append(" And ord.gs_order_category = '" + tOrderType + "' ");
		}
		if (tDeliverGoodsId != null && tDeliverGoodsId.trim().length() > 0) {
			tSql.append(" And det.gs_deliver_goods_id = '" + tDeliverGoodsId.trim() + "' ");
		}
		if(aForm.getOrderDeptId() != null ){
			tSql.append(" and ord.GS_WORKCORP_ID  like '" + aForm.getOrderDeptId() + "%'");
		}

		//图件号
		if(drawingpiececode != null && !drawingpiececode.equals("")){
			tSql.append(" and pro.DRAWING_PIECE_CODE like '%" + drawingpiececode.trim() + "%'");
		}
		return tSql.toString();
	}

	private String getOrderDetailListSqlOfBusQryNew(GSOrderDetailQueryForm aForm)throws GSException{
		String tOrderid = aForm.getOrderid();
		String tProviderName = aForm.getProviderName();
		String tOrderCreateDateStr = aForm.getOrderCreateDateStr();
		String tOrderCreateDateEnd = aForm.getOrderCreateDateEnd();
		String tDeliverGoodsId = aForm.getDeliverGoodsId();
		String tOrderDetailStatus = aForm.getOrderDetailStatus();
		String tOrderDetailStatusMx = aForm.getOrderDetailStatusMx();


		StringBuffer tSql = new StringBuffer(GSOrderDetailDAO.getOrderAndDetailAndCategorySqlNew());
		if(tOrderDetailStatusMx!=null&&"1".equals(tOrderDetailStatusMx)){
			tSql.append("  and ord.gs_order_tempid in ( select gs_order_tempid from gs_order_detail where gs_provider_id = '90001' and GS_ORDER_DETAIL_STATUS ='3')  ");
		}
		if(tOrderDetailStatusMx!=null&&"2".equals(tOrderDetailStatusMx)){
			tSql.append("  and ord.gs_order_tempid in ( select gs_order_tempid from gs_order_detail where gs_provider_id = '90001' and GS_ORDER_DETAIL_STATUS ='10')  ");
		}
		if(tOrderDetailStatusMx!=null&&"3".equals(tOrderDetailStatusMx)){
			tSql.append("  and ord.gs_order_tempid in ( select gs_order_tempid from gs_order_detail where gs_provider_id = '90001' and GS_ORDER_DETAIL_STATUS ='11')  ");
		}
		if(tOrderDetailStatusMx!=null&&"4".equals(tOrderDetailStatusMx)){
			tSql.append("  and ord.gs_order_tempid in ( select gs_order_tempid from gs_order_detail where gs_provider_id = '90001' and GS_ORDER_DETAIL_STATUS ='12')  ");
		}
		
		if (tOrderid != null && tOrderid.trim().length() > 0) {
			tSql.append(" And det.gs_order_detail_id like '%" + tOrderid.trim() + "%' ");
		}
		if (tProviderName != null && tProviderName.trim().length() > 0) {
			tSql.append(" And (prv.name like '%" + tProviderName.trim() + "%' ");
			tSql.append(" or prv.FULL_NAME like '%" + tProviderName.trim() + "%' or det.gs_provider_id='"+tProviderName.trim()+"' )");
		}
	
		if(tOrderCreateDateStr != null  && tOrderCreateDateStr.trim().length() != 0) {
			tSql.append(" and ord.GS_ORDER_CREATE_DATE >='" + tOrderCreateDateStr.substring(0,4)+"-"+tOrderCreateDateStr.substring(4,6)+"-"+tOrderCreateDateStr.substring(6,8) + "'");
		}
		if(tOrderCreateDateEnd != null  && tOrderCreateDateEnd.trim().length() != 0) {
			tSql.append(" and ord.GS_ORDER_CREATE_DATE <='" + tOrderCreateDateEnd.substring(0,4)+"-"+tOrderCreateDateEnd.substring(4,6)+ "-"+tOrderCreateDateEnd.substring(6,8) + " 23:59:59'");
		}

	
		tSql.append(" and (det.GS_ORDER_DETAIL_STATUS in ('3','10','11','12')) ");
	
		
		if (tDeliverGoodsId != null && tDeliverGoodsId.trim().length() > 0) {
			tSql.append(" And det.gs_deliver_goods_id = '" + tDeliverGoodsId.trim() + "' ");
		}
		if(aForm.getOrderDeptId() != null ){
			tSql.append(" and ord.GS_WORKCORP_ID  like '" + aForm.getOrderDeptId() + "%'");
		}
		tSql.append(" group by ord.gs_order_tempid,ord.gs_deliver_goods_addr,ord.gs_order_create_date,ord.gs_order_contact_name,ord.gs_order_contact_phone,war.full_name" );
		
		if(tOrderDetailStatus!=null&&"0000".equals(tOrderDetailStatus)){
			tSql.append(" having (sum(det.gs_order_count)> sum(det.gs_signin_count) or sum(det.gs_signin_count) is null)");
		}else if(tOrderDetailStatus!=null&&"1111".equals(tOrderDetailStatus)){
			tSql.append(" having sum(det.gs_order_count)=sum(det.gs_signin_count) ");
		}
		tSql.append(" order by ord.gs_order_create_date desc " );
		return tSql.toString();
	}
	/**
	 * 查询总金额SQL
	 * @author wp
	 * 2010-03-26
	 */
	private String getOrderDetailListSqlOfBusQryTotalAmount(GSOrderDetailQueryForm aForm)throws GSException{
		String tOrderid = aForm.getOrderid();
		String tProviderName = aForm.getProviderName();
		String tConcentricityId = aForm.getConcentricityId();
		String tManufInfo = aForm.getManufInfo();
		String tManuftype1 = aForm.getManuftype1();
		String tManuftype2 = aForm.getManuftype2();
		String tManuftype3 = aForm.getManuftype3();
		String tOrderCreateDateStr = aForm.getOrderCreateDateStr();
		String tOrderCreateDateEnd = aForm.getOrderCreateDateEnd();
		String tOrderDetailStatus = aForm.getOrderDetailStatus();
		String tOrderType = aForm.getOrderType();
		String tDeliverGoodsId = aForm.getDeliverGoodsId();
		String drawingpiececode = aForm.getDrawingpiececode();

		StringBuffer tSql = new StringBuffer(GSOrderDetailDAO.getOrderAndDetailAndCategorySqlTotalAmount());

		if (tOrderid != null && tOrderid.trim().length() > 0) {
			tSql.append(" And det.gs_order_detail_id like '%" + tOrderid.trim() + "%' ");
		}
		if (tProviderName != null && tProviderName.trim().length() > 0) {
			tSql.append(" And (prv.name like '%" + tProviderName.trim() + "%' ");
			tSql.append(" or prv.FULL_NAME like '%" + tProviderName.trim() + "%' or det.gs_provider_id='"+tProviderName.trim()+"' )");
		}
		if (tConcentricityId != null && tConcentricityId.trim().length() > 0) {
			tSql.append(" And det.gs_concentricity_id like '%" + tConcentricityId.trim() + "%' ");
		}
		if(tOrderCreateDateStr != null  && tOrderCreateDateStr.trim().length() != 0) {
			tSql.append(" and ord.GS_ORDER_CREATE_DATE >='" + tOrderCreateDateStr.substring(0,4)+"-"+tOrderCreateDateStr.substring(4,6)+"-"+tOrderCreateDateStr.substring(6,8) + "'");
		}
		if(tOrderCreateDateEnd != null  && tOrderCreateDateEnd.trim().length() != 0) {
			tSql.append(" and ord.GS_ORDER_CREATE_DATE <='" + tOrderCreateDateEnd.substring(0,4)+"-"+tOrderCreateDateEnd.substring(4,6)+ "-"+tOrderCreateDateEnd.substring(6,8) + " 23:59:59'");
		}
		if (tManufInfo != null && tManufInfo.length() > 0) {
			tSql.append(" and (pro.NAME like '%" + tManufInfo + "%'");
			tSql.append(" or pro.MODEL like '%" + tManufInfo + "%'");
			tSql.append(" or pro.SPEC like '%" + tManufInfo + "%'");
			tSql.append(" or pro.PRODUCT_BRAND like '%" + tManufInfo + "%'");
			tSql.append(" or pro.APPENDER_PROPERTY like '%" + tManufInfo + "%'");
			tSql.append(" or pro.MANUFATURER like '%" + tManufInfo + "%')");
		}

		if (tManuftype1 != null && tManuftype1.length() > 0) {
			if(tManuftype1.equals("0")){
				tSql.append(" and cat.CODE like '%'");
			}else if(tManuftype2.equals("0")){
				tSql.append(" and (cat.CATEGORY_ID1 ='" + tManuftype1 + "'");
				tSql.append(" or cat.CODE='" + tManuftype1 + "')");
			}else if(tManuftype3.equals("0")){
				tSql.append(" and (cat.CATEGORY_ID2 ='" + tManuftype2 + "'");
				tSql.append(" or cat.code='" + tManuftype2 + "')");
			}else{
				tSql.append(" and cat.code ='" + tManuftype3 + "'");
			}
		}
		if (tOrderDetailStatus != null && tOrderDetailStatus.length() > 0 && !tOrderDetailStatus.equals("0000")) {
			tSql.append(" And det.gs_order_detail_status = '" + tOrderDetailStatus + "' ");
		}else{
			tSql.append(" and (det.GS_ORDER_DETAIL_STATUS in ('3','6','7','8','9','10','11','12','13','14','18','19','20','21','22')) ");
		}
		if (tOrderType != null && tOrderType.length() > 0 && !tOrderType.equals("0000")) {
			tSql.append(" And ord.gs_order_category = '" + tOrderType + "' ");
		}
		if (tDeliverGoodsId != null && tDeliverGoodsId.trim().length() > 0) {
			tSql.append(" And det.gs_deliver_goods_id = '" + tDeliverGoodsId.trim() + "' ");
		}
		if(aForm.getOrderDeptId() != null ){
			tSql.append(" and ord.GS_WORKCORP_ID  like '" + aForm.getOrderDeptId() + "%'");
		}

		//图件号
		if(drawingpiececode != null && !drawingpiececode.equals("")){
			tSql.append(" and pro.DRAWING_PIECE_CODE like '%" + drawingpiececode.trim() + "%'");
		}
		return tSql.toString();
	}

	private String getOrderDetailListSqlOfBusQry1(GSOrderDetailQueryForm aForm,ArrayList aOrg)throws GSException{
		if(aOrg == null){
			aOrg =new ArrayList();
			String[] tTmpOrg ={"NoOrg"};
			aOrg.add(tTmpOrg);
		}
		String tOrderid = aForm.getOrderid();
		String tProviderName = aForm.getProviderName();
		String tDeliverGoodsAddrId = aForm.getDeliverGoodsAddrId();
		String tConcentricityId = aForm.getConcentricityId();
		String tManufInfo = aForm.getManufInfo();
		String tManuftype1 = aForm.getManuftype1();
		String tManuftype2 = aForm.getManuftype2();
		String tManuftype3 = aForm.getManuftype3();
		String tOrderCreateDateStr = aForm.getOrderCreateDateStr();
		String tOrderCreateDateEnd = aForm.getOrderCreateDateEnd();
		String tOrderDetailStatus = aForm.getOrderDetailStatus();
		String tOrderType = aForm.getOrderType();
		String tDeliverGoodsId = aForm.getDeliverGoodsId();

		StringBuffer tSql = new StringBuffer(
		"Select  Distinct god.gs_order_detail_id as P1,god.gs_concentricity_id as P2,gm.gs_manufacture_name as P3 ,gm.gs_is_tradeflat_manufacture as P4, ");
		tSql.append(" gm.gs_manufacture_spec as P5,gm.gs_manufacture_type as P6,gm.gs_manufacture_brand as P7,god.gs_order_count as P8,gp.GS_PROVIDER_SHORTNAME as P9, ");
		tSql.append(" gorg.gs_org_name as P10,god.gs_goods_unitprice as P11,god.gs_deliver_goods_date as P12,god.gs_deliver_goods_time as P13,god.gs_provider_id As P14,god.gs_order_tempid As P15, ");
		tSql.append(" God.Gs_Order_Detail_Status As P16, Gord.Gs_Order_Create_Date As P17,");
		tSql.append(" gm.gs_quant_unit As P18,god.GS_LASTMODIFY_DATE as P19,gp.GS_IS_TRADEFLAT_PROVIDER as P20,god.GS_LASTMODIFY_DATE as P21 ");
		tSql.append(" From gs_order_detail god,gs_manufacture gm,gs_provider gp,gs_order gord,gs_order_dept_info godi,gs_organization gorg,");
		tSql.append(" GS_MANUFACTURE_CATEGORY g");
		tSql.append(" Where (god.gs_is_balanceed = 0 or god.gs_is_prov_balanceed = 0)  And god.gs_order_tempid = gord.gs_order_tempid ");
		tSql.append(" And god.gs_provider_id = gp.gs_provider_id ");
		tSql.append(" And god.gs_manufacture_id = gm.gs_manufacture_id ");
		tSql.append(" And gord.gs_workcorp_id = godi.gs_org_id ");
		tSql.append(" And gord.GS_DELIVER_GOODS_ADDR = godi.GS_DELIVER_GOODS_ADDR ");
		tSql.append(" And gord.gs_workcorp_id = gorg.gs_org_id ");
		tSql.append(" AND gm.gs_manufacture_category_id = g.gs_manufacture_category_id");
		if (tOrderid != null && tOrderid.trim().length() > 0) {
			tSql.append(" And god.gs_order_detail_id like '%" + tOrderid.trim() + "%' ");
		}
		if (tProviderName != null && tProviderName.trim().length() > 0) {
			tSql.append(" And (gp.gs_provider_name like '%" + tProviderName.trim() + "%' ");
			tSql.append(" or gp.gs_provider_shortname like '%" + tProviderName.trim() + "%')");
		}

		if (tDeliverGoodsAddrId != null && tDeliverGoodsAddrId.length() > 0 && !tDeliverGoodsAddrId.equals("0000")) {
			tSql.append(" And godi.gs_deliver_goods_addr_id = '" + tDeliverGoodsAddrId + "' ");
		}
		if (tConcentricityId != null && tConcentricityId.trim().length() > 0) {
			tSql.append(" And god.gs_concentricity_id like '%" + tConcentricityId.trim() + "%' ");
		}
		if(tOrderCreateDateStr != null  && tOrderCreateDateStr.trim().length() != 0) {
			tSql.append(" and gord.GS_ORDER_CREATE_DATE >='" + tOrderCreateDateStr.substring(0,4)+"-"+tOrderCreateDateStr.substring(4,6)+"-"+tOrderCreateDateStr.substring(6,8) + "'");
		}
		if(tOrderCreateDateEnd != null  && tOrderCreateDateEnd.trim().length() != 0) {
			tSql.append(" and to_char(to_date(gord.GS_ORDER_CREATE_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD') <='" + tOrderCreateDateEnd.replace('/','-') + "'");
		}
		if (tManufInfo != null && tManufInfo.length() > 0) {
			tSql.append(" and (gm.GS_MANUFACTURE_NAME like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURE_TYPE like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURE_SPEC like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURE_BRAND like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_APPENDER_PROPERTY like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURER_NAME like '%" + tManufInfo + "%')");
		}

		if (tManuftype1 != null && tManuftype1.length() > 0) {
			if(tManuftype1.equals("0")){
				tSql.append(" and g.GS_MANUFACTURE_CATEGORY_ID like '%'");
			}else if(tManuftype2.equals("0")){
				tSql.append(" and (g.GS_MANUFACTURE_CATEGORY_ID1 ='" + tManuftype1 + "'");
				tSql.append(" or g.GS_MANUFACTURE_CATEGORY_ID='" + tManuftype1 + "')");
			}else if(tManuftype3.equals("0")){
				tSql.append(" and (g.GS_MANUFACTURE_CATEGORY_ID2 ='" + tManuftype2 + "'");
				tSql.append(" or g.GS_MANUFACTURE_CATEGORY_ID='" + tManuftype2 + "')");
			}else{
				tSql.append(" and g.GS_MANUFACTURE_CATEGORY_ID ='" + tManuftype3 + "'");
			}
		}

		if (tOrderDetailStatus != null && tOrderDetailStatus.length() > 0 && !tOrderDetailStatus.equals("0000")) {
			tSql.append(" And god.gs_order_detail_status = '" + tOrderDetailStatus + "' ");
		}else{
			tSql.append(" and (god.GS_ORDER_DETAIL_STATUS in ('6','7','8','9','10','11','12','13','14','18','19','20','21','22')) ");
			//tSql.append(" and (god.GS_ORDER_DETAIL_STATUS ='6' or god.GS_ORDER_DETAIL_STATUS ='7' or god.GS_ORDER_DETAIL_STATUS ='8' or god.GS_ORDER_DETAIL_STATUS ='9' ");
			//tSql.append("or god.GS_ORDER_DETAIL_STATUS ='10' or god.GS_ORDER_DETAIL_STATUS ='11' or god.GS_ORDER_DETAIL_STATUS ='12' or god.GS_ORDER_DETAIL_STATUS ='14' ");
			//tSql.append("or god.GS_ORDER_DETAIL_STATUS ='21' or god.GS_ORDER_DETAIL_STATUS ='22')");
		}
		if (tOrderType != null && tOrderType.length() > 0 && !tOrderType.equals("0000")) {
			tSql.append(" And gord.gs_order_category = '" + tOrderType + "' ");
		}
		if (tDeliverGoodsId != null && tDeliverGoodsId.trim().length() > 0) {
			//tSql.append(" And god.gs_deliver_goods_id like '%" + tDeliverGoodsId.trim() + "%' ");
			//20060802 by simonyi 送货单号完全匹配查询
			tSql.append(" And god.gs_deliver_goods_id = '" + tDeliverGoodsId.trim() + "' ");
		}

		if(aForm.getOrderDeptId() != null && !aForm.getOrderDeptId().equals("0000")){
			tSql.append(" and gord.GS_WORKCORP_ID ='" + aForm.getOrderDeptId() + "'");
		}else{
			if(aOrg != null && aOrg.size() > 0){
				tSql.append(" and gord.GS_WORKCORP_ID in(");
				for(int i=0; i<aOrg.size();i++){
					String tStr[] = (String[])aOrg.get(i);
					if( i == aOrg.size()-1){
						tSql.append("'"+tStr[0]+"')");
					}else{
						tSql.append("'"+tStr[0]+"',");
					}
				}
			}
		}
		tSql.append(" and god.gs_balance_mode='0' ");
		return tSql.toString();
	}
	/**
	 * 获取网上订单收货跟踪信息
	 * @param aConn             数据库链接
	 * @param aForm             Form对象
	 * @param aOrg              组织对象
	 * @return
	 * @throws GSException
	 */
	public ArrayList getOrderDetailList(Connection aConn, GSOrderDetailQueryForm aForm,int pageNo,int pageSize) throws GSException {
		GSOrderDetailQueryVO tOrderVO = new GSOrderDetailQueryVO();
		StringBuffer tSql = new StringBuffer(getOrderDetailListSqlOfBusQry(aForm));
		tSql.append(" order by det.GS_LASTMODIFY_DATE desc");
		ArrayList tList = null;
		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tList = this.executeQuery(aConn, new GSOrderAnaDetailAndCategoryBean(), tSql.toString(), tObj,pageNo,pageSize);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}

		return tList;
	}

	/**
	 * 得到所查询订单的总金额
	 * @author wp
	 * 2010-03-26
	 */
	public double getOrderDetailTotalAmount(Connection aConn, GSOrderDetailQueryForm aForm) throws GSException {
		GSOrderDetailQueryVO tOrderVO = new GSOrderDetailQueryVO();
		StringBuffer tSql = new StringBuffer(getOrderDetailListSqlOfBusQryTotalAmount(aForm));
		PreparedStatement tStatement = null;
		ResultSet tResultSet = null;
		double totalAmount = 0d;
		GSLogWriter.debug(this, tSql.toString());
		try {
			tStatement = aConn.prepareStatement(tSql.toString());
			tResultSet = tStatement.executeQuery();
			if(tResultSet.next()) {
				totalAmount = tResultSet.getDouble(1);
			}
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}

		return totalAmount;
	}

	/**
	 * 获取网上订单收货跟踪信息
	 * @param aConn             数据库链接
	 * @param aForm             Form对象
	 * @param aOrg              组织对象
	 * @return
	 * @throws GSException
	 */
	public ArrayList getOrderDetailListForSW(Connection aConn, GSOrderDetailQueryForm aForm,int pageNo,int pageSize) throws GSException {
		GSOrderDetailQueryForSWVO tOrderVO = new GSOrderDetailQueryForSWVO();
		StringBuffer tSql = new StringBuffer(getOrderDetailListSqlOfBusQry(aForm));
		tSql.append(" order by det.GS_LASTMODIFY_DATE desc");
		ArrayList tList = null;
		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tList = this.executeQuery(aConn, new GSOrderAnaDetailAndCategoryBean(), tSql.toString(), tObj,pageNo,pageSize);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}

		return tList;
	}
	
	/**
	 * 获取网上订单收货跟踪信息
	 * @param aConn             数据库链接
	 * @param aForm             Form对象
	 * @param aOrg              组织对象
	 * @return
	 * @throws GSException
	 */
	public ArrayList getOrderDetailListForSWNew(Connection aConn, GSOrderDetailQueryForm aForm,int pageNo,int pageSize) throws GSException {
		GSOrderDetailQueryForSWVO tOrderVO = new GSOrderDetailQueryForSWVO();
		StringBuffer tSql = new StringBuffer(getOrderDetailListSqlOfBusQryNew(aForm));
		ArrayList tList = null;
		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tList = this.executeQuery(aConn, new GSOrderAnaDetailAndCategoryNewBean(), tSql.toString(), tObj,pageNo,pageSize);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}

		return tList;
	}


	/**
	 * 为结算审核获取订单信息
	 * @param aConn
	 * @param aForm
	 * @param aOrg
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws GSException
	 */
	public ArrayList getOrderDetailListForBalanceAudit(Connection aConn, GSOrderDetailQueryForm aForm,ArrayList aOrg,int pageNo,int pageSize) throws GSException {
		GSOrderDetailQueryVO tOrderVO = new GSOrderDetailQueryVO();
		StringBuffer tSql = new StringBuffer(getOrderDetailListSqlOfBusQryForBalanceAudit(aForm,aOrg));
		tSql.append(" order by god.GS_LASTMODIFY_DATE desc");
		ArrayList tList = null;
		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tList = this.executeQuery(aConn, tOrderVO, tSql.toString(), tObj,pageNo,pageSize);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}

		return tList;
	}
	public int getCountsByOrderStatus(String tempid,String status) throws GSException {
		StringBuffer tSql = new StringBuffer("select count(*) from gs_order_detail  where gs_order_tempid='"+tempid+"'");
		tSql.append(" and gs_provider_id='90001' and gs_order_detail_status='"+status+"'");
		int tSize = 0;
		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tSize = this.queryRowNum(tSql.toString(), tObj);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}

		return tSize;
	}
	
	public int getCountsByMoreOrderStatus(String tempids,String status) throws GSException {
		String[] ordersId=tempids.split(",");
		String strs="";
		for(int i=0;i<ordersId.length;i++){
			strs+="'"+ordersId[i]+"',";
		}
		strs=strs.substring(0,strs.length()-1);
		StringBuffer tSql = new StringBuffer("select count(*) from gs_order_detail  where gs_order_tempid in ("+strs+")");
		tSql.append(" and gs_provider_id='90001' and gs_order_detail_status='"+status+"'");
		int tSize = 0;
		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tSize = this.queryRowNum(tSql.toString(), tObj);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}

		return tSize;
	}


	public int getOrderDetailListCountForBalanceAudit(GSOrderDetailQueryForm aForm,ArrayList aOrg) throws GSException {
		StringBuffer tSql = new StringBuffer("select count(*) from(");
		tSql.append(getOrderDetailListSqlOfBusQryForBalanceAudit(aForm,aOrg));
		tSql.append(")");
		int tSize = 0;
		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tSize = this.queryRowNum(tSql.toString(), tObj);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}

		return tSize;
	}
	public int getOrderDetailListCount(GSOrderDetailQueryForm aForm) throws GSException {
		StringBuffer tSql = new StringBuffer("select count(*) from(");
		tSql.append(getOrderDetailListSqlOfBusQry(aForm));
		tSql.append(")");
		int tSize = 0;
		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tSize = this.queryRowNum(tSql.toString(), tObj);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}

		return tSize;
	}
	public int getOrderDetailListCountNew(GSOrderDetailQueryForm aForm) throws GSException {
		StringBuffer tSql = new StringBuffer("select count(*) from(");
		tSql.append(getOrderDetailListSqlOfBusQryNew(aForm));
		tSql.append(")");
		int tSize = 0;
		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tSize = this.queryRowNum(tSql.toString(), tObj);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}

		return tSize;
	}
	public int getOrderDetailListCount1(GSOrderDetailQueryForm aForm,ArrayList aOrg) throws GSException {
		StringBuffer tSql = new StringBuffer("select count(*) from(");
		//tSql.append(this.getOrderDetailListSqlOfBusQry(aForm,aOrg));
		tSql.append(getOrderDetailListSqlOfBusQry1(aForm,aOrg));
		tSql.append(")");
		int tSize = 0;
		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tSize = this.queryRowNum(tSql.toString(), tObj);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}

		return tSize;
	}
	private String getOrderDetailListSql(GSOrderDetailQueryForm aForm,ArrayList aOrg)throws GSException{
		if(aOrg == null){
			aOrg =new ArrayList();
			String[] tTmpOrg ={"NoOrg"};
			aOrg.add(tTmpOrg);
		}
		String tOrderid = aForm.getOrderid();
		String tProviderName = aForm.getProviderName();
		String tDeliverGoodsAddrId = aForm.getDeliverGoodsAddrId();
		String tConcentricityId = aForm.getConcentricityId();
		String tManufInfo = aForm.getManufInfo();
		String tManuftype1 = aForm.getManuftype1();
		String tManuftype2 = aForm.getManuftype2();
		String tManuftype3 = aForm.getManuftype3();
		String tOrderCreateDateStr = aForm.getOrderCreateDateStr();
		String tOrderCreateDateEnd = aForm.getOrderCreateDateEnd();
		String tOrderDetailStatus = aForm.getOrderDetailStatus();
		String tOrderType = aForm.getOrderType();
		String tDeliverGoodsId = aForm.getDeliverGoodsId();

		StringBuffer tSql = new StringBuffer(
		"Select  Distinct god.gs_order_detail_id as P1,god.gs_concentricity_id as P2,gm.gs_manufacture_name as P3 ,gm.gs_is_tradeflat_manufacture as P4, ");
		tSql.append(" gm.gs_manufacture_spec as P5,gm.gs_manufacture_type as P6,gm.gs_manufacture_brand as P7,god.gs_order_count as P8,gp.GS_PROVIDER_SHORTNAME as P9, ");
		tSql.append(" gorg.gs_org_name as P10,gm.gs_manufacture_unitprice as P11,god.gs_deliver_goods_date as P12,god.gs_deliver_goods_time as P13,god.gs_provider_id As P14,god.gs_order_tempid As P15, ");
		tSql.append(" God.Gs_Order_Detail_Status As P16, Gord.Gs_Order_Create_Date As P17, ");
		tSql.append(" gm.gs_quant_unit As P18,god.GS_LASTMODIFY_DATE as P19,gp.GS_IS_TRADEFLAT_PROVIDER as P20 ");
		tSql.append(" From gs_order_detail god,gs_manufacture gm,gs_provider gp,gs_order gord,gs_order_dept_info godi,gs_organization gorg ");
		tSql.append(" Where god.gs_manufacture_id = gm.gs_manufacture_id ");
		tSql.append(" And god.gs_provider_id = gp.gs_provider_id ");
		tSql.append(" And god.gs_order_tempid = gord.gs_order_tempid ");
		tSql.append(" And gord.gs_workcorp_id = godi.gs_org_id ");
		tSql.append(" And gord.gs_workcorp_id = gorg.gs_org_id ");
		tSql.append(" And gm.gs_is_tradeflat_manufacture = '0' ");
		if (tOrderid != null && tOrderid.trim().length() > 0) {
			tSql.append(" And god.gs_order_detail_id like '%" + tOrderid.trim() + "%' ");
		}
		if (tProviderName != null && tProviderName.trim().length() > 0) {
			tSql.append(" And gp.gs_provider_name like '%" + tProviderName.trim() + "%' ");
		}

		if (tDeliverGoodsAddrId != null && tDeliverGoodsAddrId.length() > 0 && !tDeliverGoodsAddrId.equals("0000")) {
			tSql.append(" And godi.gs_deliver_goods_addr_id = '" + tDeliverGoodsAddrId + "' ");
		}
		if (tConcentricityId != null && tConcentricityId.trim().length() > 0) {
			tSql.append(" And god.gs_concentricity_id like '%" + tConcentricityId.trim() + "%' ");
		}
		if(tOrderCreateDateStr != null  && tOrderCreateDateStr.trim().length() != 0) {
			tSql.append(" and gord.GS_ORDER_CREATE_DATE >='" + tOrderCreateDateStr.substring(0,4)+"-"+tOrderCreateDateStr.substring(4,6)+"-"+tOrderCreateDateStr.substring(6,8) + "'");
		}
		if(tOrderCreateDateEnd != null  && tOrderCreateDateEnd.trim().length() != 0) {
			tSql.append(" and to_char(to_date(gord.GS_ORDER_CREATE_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD') <='" + tOrderCreateDateEnd.replace('/','-') + "'");
		}
		if (tManufInfo != null && tManufInfo.length() > 0) {
			tSql.append(" and (gm.GS_MANUFACTURE_NAME like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURE_TYPE like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURE_SPEC like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURE_BRAND like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_APPENDER_PROPERTY like '%" + tManufInfo + "%'");
			tSql.append(" or gm.GS_MANUFACTURER_NAME like '%" + tManufInfo + "%')");
		}

		if (tManuftype1 != null && tManuftype1.length() > 0) {
			if (tManuftype1.equals("0")) {
				tSql.append(" and gm.GS_MANUFACTURE_CATEGORY_ID like '%'");
			} else if (tManuftype2.equals("0")) {
				tSql.append(" and gm.GS_MANUFACTURE_CATEGORY_ID like '%" + tManuftype1.substring(0,1) + "%'");
			} else if (tManuftype3.equals("0")) {
				tSql.append(" and gm.GS_MANUFACTURE_CATEGORY_ID like '%" + tManuftype2.substring(0,2) + "%'");
			} else {
				tSql.append(" and gm.GS_MANUFACTURE_CATEGORY_ID like '%" + tManuftype3 + "%'");
			}
		}

		if (tOrderDetailStatus != null && tOrderDetailStatus.length() > 0 && !tOrderDetailStatus.equals("0000")) {
			tSql.append(" And god.gs_order_detail_status = '" + tOrderDetailStatus + "' ");
		}else{
			tSql.append(" and (god.GS_ORDER_DETAIL_STATUS ='6' or god.GS_ORDER_DETAIL_STATUS ='7' or god.GS_ORDER_DETAIL_STATUS ='8' or god.GS_ORDER_DETAIL_STATUS ='9' ");
			tSql.append("or god.GS_ORDER_DETAIL_STATUS ='10' or god.GS_ORDER_DETAIL_STATUS ='11' or god.GS_ORDER_DETAIL_STATUS ='12' or god.GS_ORDER_DETAIL_STATUS ='14' ");
			tSql.append("or god.GS_ORDER_DETAIL_STATUS ='21' or god.GS_ORDER_DETAIL_STATUS ='22')");
		}
		if (tOrderType != null && tOrderType.length() > 0 && !tOrderType.equals("0000")) {
			tSql.append(" And gord.gs_order_category = '" + tOrderType + "' ");
		}
		if (tDeliverGoodsId != null && tDeliverGoodsId.trim().length() > 0) {
			tSql.append(" And god.gs_deliver_goods_id like '%" + tDeliverGoodsId.trim() + "%' ");
		}

		if(aForm.getOrderDeptId() != null && !aForm.getOrderDeptId().equals("0000")){
			tSql.append(" and gord.GS_WORKCORP_ID ='" + aForm.getOrderDeptId() + "'");
		}else{
			if(aOrg != null && aOrg.size() > 0){
				tSql.append(" and gord.GS_WORKCORP_ID in(");
				for(int i=0; i<aOrg.size();i++){
					String tStr[] = (String[])aOrg.get(i);
					if( i == aOrg.size()-1){
						tSql.append("'"+tStr[0]+"')");
					}else{
						tSql.append("'"+tStr[0]+"',");
					}
				}
			}
		}
		return tSql.toString();
	}
	public GSBusOrderDetailDeliverVO getGSBusOrderDetailDeliverInfo(String aOrderDetailId)
	throws GSException{
		GSBusOrderDetailDeliverVO tGSOrdeVO = new GSBusOrderDetailDeliverVO();
		String sql = "Select a.gs_deliver_goods_id As P1,a.gs_deliver_goods_date As P2,a.gs_deliver_goods_time As P3 , "
			+ "a.gs_deliveryman As P4,a.gs_deliver_goods_phone As P5,a.gs_deliver_goods_counts As P6 , "
			+ "a.gs_deliver_goods_confirm_date As P7,a.gs_deliver_goods_confirm_time As P8,b.gs_quant_unit As P9 "
			+ "From gs_order_detail a,gs_manufacture b "
			+ "Where a.gs_manufacture_id = b.gs_manufacture_id And "
			+ "a.gs_order_detail_id = ? ";

		ArrayList tList = null;

		Object[] tObj = { aOrderDetailId };
		try {
			tList = this.executeQuery(new GSBusOrderDetailDeliverVO(), sql, tObj);
			if (tList.size() == 1) {
				tGSOrdeVO = (GSBusOrderDetailDeliverVO) tList.get(0);
			} else {
				// throw new
				// GSException(GSException.BME20041,GSException.BME20041_MSG);
			}
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}

		return tGSOrdeVO;
	}

	/**
	 * 查询应急采购信息列表
	 */
	public ArrayList queryEmergencyOrder(Connection aConn, GSOrderEmergencyVO aGSOrderEmergencyVO,ArrayList aOrg) throws GSException {

		if(aOrg == null){
			aOrg =new ArrayList();
			String[] tTmpOrg ={"NoOrg"};
			aOrg.add(tTmpOrg);
		}

		GSOrderEmergencyOfWorkingVO tOrderVO = new GSOrderEmergencyOfWorkingVO();

		StringBuffer tSql = new StringBuffer("select Distinct A.GS_DELIVER_GOODS_ID as P1,A.GS_ORDER_CATEGORY as P2,");
		tSql.append("A.GS_ORDER_DETAIL_STATUS as P3,D.GS_ORG_NAME as P4,A.GS_CONCENTRICITY_ID as P5,");
		tSql.append("B.GS_MANUFACTURE_NAME as P6 ,B.GS_MANUFACTURE_SPEC as P7,B.GS_MANUFACTURE_TYPE as P8,");
		tSql.append("B.GS_MANUFACTURE_BRAND as P9,E.GS_PROVIDER_SHORTNAME as P10,A.GS_ORDER_COUNT as P11,");
		tSql.append("A.GS_SIGNIN_COUNT as P12,A.GS_SIGNIN_PRICE as P13,A.GS_SIGNIN_DATE as P14,B.GS_QUANT_UNIT as P15,");
		tSql.append("A.GS_ORDER_TEMPID as P16,A.GS_ORDER_DETAIL_ID as P17,A.GS_LASTMODIFY_DATE as P18,a.gs_balance_mode2 as P19 ");
		tSql.append(" from GS_ORDER_TEMP_DETAIL A,GS_MANUFACTURE B,GS_ORDER_DEPT_INFO C,GS_ORGANIZATION D,GS_PROVIDER E");
		tSql.append(" where A.GS_CONCENTRICITY_ID=B.GS_CONCENTRICITY_ID and");
		tSql.append(" (A.gs_manufacture_id is null or (A.gs_manufacture_id is not null and A.gs_manufacture_id = B.gs_manufacture_id))");
		tSql.append(" and A.GS_WORKCORP_ID=D.GS_ORG_ID and A.GS_PROVIDER_ID=E.GS_PROVIDER_ID");
		//tSql.append(" and A.GS_DELIVER_GOODS_ADDR = D.GS_DELIVER_GOODS_ADDR");

		ArrayList tList = null;

		if (aGSOrderEmergencyVO.goodsid != null) {
			tSql.append(" and A.GS_DELIVER_GOODS_ID like '%" + aGSOrderEmergencyVO.goodsid + "%'");
		}

		if (aGSOrderEmergencyVO.orderDeptId != null) {
			if (aGSOrderEmergencyVO.orderDeptId.equals("0000")) {
				if (aOrg.size() > 0) {
					tSql.append(" and A.GS_WORKCORP_ID in(");
					for (int i = 0; i < aOrg.size(); i++) {
						String tStr[] = (String[]) aOrg.get(i);
						if (i == aOrg.size() - 1) {
							tSql.append("'" + tStr[0] + "')");
						} else {
							tSql.append("'" + tStr[0] + "',");
						}
					}
				}
			} else {
				tSql.append(" and A.GS_WORKCORP_ID ='" + aGSOrderEmergencyVO.orderDeptId + "'");
			}
		}


		//if (aGSOrderEmergencyVO.deliverGoodsAddrId.equals("0000")) {
		//    tSql.append(" and C.GS_DELIVER_GOODS_ADDR_ID like '%'");
		//} else {
		//    tSql.append(" and C.GS_DELIVER_GOODS_ADDR_ID ='" + aGSOrderCancelVO.deliverGoodsAddrId + "'");
		//}


		if (aGSOrderEmergencyVO.category != null) {
			tSql.append(" and A.GS_ORDER_CATEGORY like '%" + aGSOrderEmergencyVO.category + "%'");
		}

		if (aGSOrderEmergencyVO.orderStatus != null) {
			tSql.append(" and A.GS_ORDER_DETAIL_STATUS like '%" + aGSOrderEmergencyVO.orderStatus + "%'");
		}

		if (aGSOrderEmergencyVO.concentricityid != null) {
			tSql.append(" and A.GS_CONCENTRICITY_ID like '%" + aGSOrderEmergencyVO.concentricityid + "%'");
		}

		if (!aGSOrderEmergencyVO.providerId.equals("")) {
			tSql.append(" and E.GS_PROVIDER_SHORTNAME = '" + aGSOrderEmergencyVO.providerId + "'");
		}

		if (aGSOrderEmergencyVO.manufacturecategory != null) {
			tSql.append(" and B.GS_MANUFACTURE_CATEGORY_ID like '%" + aGSOrderEmergencyVO.manufacturecategory + "%'");
		}
		//判断VO对象中产品种类是否存在
		if(aGSOrderEmergencyVO.Manuftype1 != null){
			if(aGSOrderEmergencyVO.Manuftype1.equals("0")){
				tSql.append(" and B.GS_MANUFACTURE_CATEGORY_ID like '%'");
			}else if(aGSOrderEmergencyVO.Manuftype2.equals("0")){
				tSql.append(" and B.GS_MANUFACTURE_CATEGORY_ID like '%" + aGSOrderEmergencyVO.Manuftype1.substring(0,1) + "%'");
			}else if(aGSOrderEmergencyVO.Manuftype3.equals("0")){
				tSql.append(" and B.GS_MANUFACTURE_CATEGORY_ID like '%" + aGSOrderEmergencyVO.Manuftype2.substring(0,2) + "%'");
			}else{
				tSql.append(" and B.GS_MANUFACTURE_CATEGORY_ID like '%" + aGSOrderEmergencyVO.Manuftype3 + "%'");
			}
		}


		if (aGSOrderEmergencyVO.manufInfo != null) {
			tSql.append(" and (B.GS_MANUFACTURE_NAME like '%" + aGSOrderEmergencyVO.manufInfo + "%'");
			tSql.append(" or B.GS_MANUFACTURE_TYPE like '%" + aGSOrderEmergencyVO.manufInfo + "%'");
			tSql.append(" or B.GS_MANUFACTURE_SPEC like '%" + aGSOrderEmergencyVO.manufInfo + "%'");
			tSql.append(" or B.GS_MANUFACTURE_BRAND like '%" + aGSOrderEmergencyVO.manufInfo + "%'");
			tSql.append(" or B.GS_MANUFACTURER_NAME like '%" + aGSOrderEmergencyVO.manufInfo + "%')");
		}


		if(aGSOrderEmergencyVO.firstdate != null  && aGSOrderEmergencyVO.firstdate.trim().length() != 0) {
			tSql.append(" and A.GS_CREATE_TIME >='" + aGSOrderEmergencyVO.firstdate.substring(0,4)+"-"+aGSOrderEmergencyVO.firstdate.substring(4,6)+"-"+aGSOrderEmergencyVO.firstdate.substring(6,8) + "'");
		}
		if(aGSOrderEmergencyVO.lastdate != null  && aGSOrderEmergencyVO.lastdate.trim().length() != 0) {
			tSql.append(" and A.GS_CREATE_TIME <='" + aGSOrderEmergencyVO.lastdate.substring(0,4)+"-"+aGSOrderEmergencyVO.lastdate.substring(4,6)+"-"+aGSOrderEmergencyVO.lastdate.substring(6,8) + " 23:59:59'");
		}


		//tSql.append(" order by A.GS_ORDER_CREATE_DATE desc");
		tSql.append(" order by A.GS_LASTMODIFY_DATE desc");
		GSLogWriter.debug(this, tSql.toString());
		Object[] tObj = {};
		try {
			tList = this.executeQuery(aConn, tOrderVO, tSql.toString(), tObj);
		} catch (Exception e) {
			GSLogWriter.error(this, e.toString());
			throw new GSException(GSException.E19999, GSException.E19999_MSG);
		}
		return tList;
	}

	/**
	 * 在应急采购信息列表中删除一笔记录
	 * 
	 * @author owen
	 * 
	 * @param aProviderId 供应商编号
	 * 
	 */
	public boolean delete(String aTempId,String aDetailId) throws GSException {
		String tSql = "delete from GS_ORDER_TEMP_DETAIL where GS_ORDER_TEMPID=? and GS_ORDER_DETAIL_ID=?";
		Object[] tObject = { aTempId,aDetailId };
		boolean tResult = false;
		try {
			tResult=executeUpdate(tSql, tObject);
		} catch (Exception ex) {
			GSLogWriter.error(this, ex.getMessage());
			throw new GSException(ex.getMessage());
		}

		return tResult;
	}

	/**
	 * 根据订单编号查询应急采购信息基本信息
	 * 
	 * @param aOrderTempID
	 *            订单编号
	 * @return GSOrderBean 应急采购信息
	 * @throws GSException
	 * @author Road Zheng
	 */
	public GSTempOrderBean getEmergencyOrderByTempID(String aOrderTempID) throws GSException {
		GSTempOrderBean tResult = null;
		ArrayList tArrayList = null;
		try {
			StringBuffer tSql = new StringBuffer("select * ");
			tSql.append("from GS_ORDER_TEMP_DETAIL ");
			tSql.append("where GS_ORDER_TEMPID = ?");
			GSTempOrderBean tGSTempOrderBean = new GSTempOrderBean();
			Object[] tObj = { aOrderTempID };
			tArrayList = this.executeQuery(tGSTempOrderBean, tSql.toString(), tObj);
		} catch (Exception sqle) {
			throw new GSException(GSException.E20043, GSException.E20043_MSG);
		}

		if (tArrayList != null && tArrayList.size() > 0) {
			tResult = (GSTempOrderBean) tArrayList.get(0);
		}
		return tResult;
	}
	/**
	 * 将应急采购信息插入订单基本表
	 * @param aOrderBean
	 * @param aConn
	 * @return
	 * @throws GSException
	 */
	public boolean insertOrderFromTemp(GSOrderBean aOrderBean,Connection aConn) throws GSException{
		boolean tFlag = false;
		StringBuffer tSql = new StringBuffer("insert into GS_ORDER (GS_ORDER_TEMPID,GS_WORKCORP_ID,GS_ORDER_CATEGORY,");
		tSql.append("GS_ORDER_STATUS,GS_NOTES,GS_RECORDER_ID,GS_ORDER_CREATE_DATE,GS_FIRSTAUDITOR_ID,GS_FIRSTAUDIT_ADVICES,");
		tSql.append("GS_FIRSTAUDIT_DATE,GS_REVIEWER_ID,GS_REVIEW_ADVICES,GS_REVIEW_DATE,GS_SUBMITER_ID1,GS_SUBMIT_DATE1,");
		tSql.append("GS_LASTMODIFER_ID,GS_LASTMODIFY_CONTENTS,GS_LASTMODIFY_DATE,GS_ORDER_SUM,GS_DELIVER_GOODS_ADDR,GS_ORDER_CONTACT_NAME,");
		tSql.append("GS_ORDER_CONTACT_PHONE,GS_DELIVER_MODE,GS_BALANCE_MODE) values ");
		tSql.append("(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		Object[] tObj = new Object[24];
		tObj[0] = aOrderBean.getGs_order_tempid();
		tObj[1] = aOrderBean.getGs_workcorp_id();
		tObj[2] = aOrderBean.getGs_order_category();
		tObj[3] = aOrderBean.getGs_order_status();
		tObj[4] = aOrderBean.getGs_notes();
		tObj[5] = aOrderBean.getGs_recorder_id();
		tObj[6] = aOrderBean.getGs_order_create_date();
		tObj[7] = aOrderBean.getGs_firstauditor_id();
		tObj[8] = aOrderBean.getGs_firstaudit_advices();
		tObj[9] = aOrderBean.getGs_firstaudit_date();
		tObj[10] = aOrderBean.getGs_reviewer_id();
		tObj[11] = aOrderBean.getGs_review_advices();
		tObj[12] = aOrderBean.getGs_review_date();
		tObj[13] = aOrderBean.getGs_submiter_id1();
		tObj[14] = aOrderBean.getGs_submit_date1();
		tObj[15] = aOrderBean.getGs_lastmodifer_id();
		tObj[16] = aOrderBean.getGs_lastmodify_contents();
		tObj[17] = aOrderBean.getGs_lastmodify_date();
		tObj[18] = new Double(aOrderBean.getGs_order_sum());
		tObj[19] = aOrderBean.getGs_deliver_goods_addr();
		tObj[20] = aOrderBean.getGs_order_contact_name();
		tObj[21] = aOrderBean.getGs_order_contact_phone();
		tObj[22] = aOrderBean.getGs_deliver_mode();
		tObj[23] = aOrderBean.getGs_balance_mode();
		try{
			tFlag = this.executeUpdate(aConn,tSql.toString(),tObj);
		}catch(Exception e){
			GSLogWriter.error(this,e.toString());
			throw new GSException(GSException.E19995,GSException.E19995_MSG);
		}
		return tFlag;
	}

	/**
	 * 建立一个新的订单
	 * @param vo 订单数据值对象
	 * @param ary 订单详细数据对象矢量
	 * @throws GSException
	 */
	public String insert4Deliver(GSOperatorBean operBean,GSDeliverOrderSaveVO vo,ArrayList ary) throws GSException {

		Connection conn = getConnetion();

		IDGenerator idg = new IDGenerator();

		String returnValue = "";
		try{
			conn.setAutoCommit(false);

			//构造订单号
			long id = idg.generatorID(vo.gs_workcorp_id,"02");
			String orderId = vo.gs_workcorp_id + idg.formatID(id,4) + "L";
			returnValue = orderId;


			//1 构造生成时间
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String createDate = sdf.format(new Date(System.currentTimeMillis()));

			//5 构造订单sql语句
			String sqlo = "insert into GS_ORDER (GS_ORDER_TEMPID,GS_WORKCORP_ID,"+
			"GS_ORDER_CATEGORY,GS_ORDER_STATUS,GS_NOTES,GS_RECORDER_ID,"+
			"GS_ORDER_CREATE_DATE,GS_DELIVER_GOODS_ADDR," +
			"GS_ORDER_CONTACT_NAME,GS_ORDER_CONTACT_PHONE," +
			"GS_DELIVER_MODE,GS_BALANCE_MODE,GS_ORDER_SUM) values (?,?,?,?,?,?,?,?,?,?,?,?,'0')";
			PreparedStatement pstmto = conn.prepareStatement(sqlo);
			pstmto.setString(1,orderId);
			pstmto.setString(2,vo.gs_workcorp_id);
			pstmto.setString(3,"1");
			pstmto.setString(4,"12");
			pstmto.setString(5,vo.gs_notes);
			pstmto.setString(6,vo.operId);
			pstmto.setString(7,createDate);
			pstmto.setString(8,vo.gs_deliver_goods_addr);
			pstmto.setString(9,vo.gs_deliveryman);
			pstmto.setString(10,vo.gs_deliver_goods_phone);
			pstmto.setString(11,vo.gs_deliver_mode);
			pstmto.setString(12,vo.gs_balance_mode);
			pstmto.executeUpdate();

			//2 构造订单明细sql语句
			PreparedStatement pstmt = null;
			String sqlm = "insert into GS_ORDER_DETAIL (" +
			"GS_ORDER_TEMPID," +
			"GS_ORDER_DETAIL_ID," +
			"GS_ORDER_DETAIL_STATUS," +
			"GS_CONCENTRICITY_ID," +
			"GS_MANUFACTURE_ID," +
			"GS_ORDER_COUNT," +
			"GS_PROVIDER_ID," +
			"GS_DELIVER_GOODS_ID," +
			"GS_GOODS_UNITPRICE," +
			"GS_SIGNIN_DATE," +
			"GS_SIGNIN_COUNT," +
			"GS_SIGNIN_PERSON," +
			"GS_IS_BALANCEED," +
			"gs_is_prov_balanceed,"+
			"GS_ORDER_DETAIL_NOTE," +
			"gs_balance_mode"+
			") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";


			pstmt = conn.prepareStatement(sqlm);

			//4 对每一个产品
			for(int i=0;i<ary.size();i++){
				String[] strb = (String[])ary.get(i);
				//manufactureId,vo.orderCount,providerId,deliverGoodsId,signinPrice[i],signinDate,signinPerson
				//构造订单明细信息号
				String orderDetailId = orderId + idg.formatID(i,2);

				pstmt.setString(1,orderId);//
				pstmt.setString(2,orderDetailId);//
				pstmt.setString(3,"12");//
				pstmt.setString(4,strb[0]);//
				pstmt.setString(5,strb[0]);//
				pstmt.setString(6,strb[1]);//
				pstmt.setString(7,strb[2]);//
				pstmt.setString(8,strb[3]);//
				pstmt.setString(9,strb[4]);//
				pstmt.setString(10,strb[5]);//
				pstmt.setString(11,strb[1]);//
				pstmt.setString(12,strb[6]);//
				pstmt.setString(13,"0");//
				pstmt.setString(14,"0");//
				pstmt.setString(15,strb[7]);//
				pstmt.setString(16, strb[8]);
				//存入数据库
				pstmt.addBatch();
			}

			//6 提交订单明细
			pstmt.executeBatch();
			conn.commit();
			if(pstmt!=null) {
				pstmt.close();
			}

		}catch(Exception e){
			try{
				conn.rollback();
			}catch(SQLException sqle){}
			throw new GSException (GSException.E20051,GSException.E20051_MSG);
		}finally{
			try{
				conn.setAutoCommit(true);
			}catch(SQLException sqle){}
			close(conn);
		}

		return returnValue;

	}


	public String getWorkCorp(String orderCode) throws GSException {
		String rt = "";
		String orgid = "";
		orgid = selectOrderByCode(orderCode).getGs_workcorp_id();
		if (orgid == null ) {
			GSLogWriter.error("WorkCorp ID is null");
			return rt;
		}
		if (orgid.length() != 4 ) {
			GSLogWriter.error("WorkCorp ID length isn't 4");
			return rt;
		}
		GSOrganizationDAO orgdao = new GSOrganizationDAO();
		if (orgid.substring(2,4).equals("00")){
			rt = orgdao.queryOrgName(orgid);
		}
		else{
			rt = orgdao.getParentOrg(orgid).getGs_org_name();
		}

		return rt;
	}

}
